SQL Server 触发器

SQL Server

触发器

测试表

1> CREATE

TABLE test_trigger_table (

2>

id

INT,

3>

name

VARCHAR(10),

4>

val

INT

5> );

6> go

INSERT

注：

SQL Server

中。

没有

BEFORE INSERT

或者

AFTER INSERT

根据文档显示，以及代码的测试。基本上类似于

Oracle

的

AFTER INSERT

SQL Sercer

触发器

没有

FOR EACH ROW

关键字

一次更新一条，还是多条，取决于

INSERTED

里面的内容。

1> CREATE TRIGGER BeforeInsertTest

2>

ON

test_trigger_table

3>

FOR INSERT

4> AS

5> DECLARE

6>

@Id

INT,

7>

@Name VARCHAR(10);

8> BEGIN

9>

PRINT('BEFORE

INSERT');

10>

SELECT @Id = id, @Name = name FROM

INSERTED;

11>

PRINT('New Name = ' + @Name);

12>

UPDATE test_trigger_table SET val =

100 WHERE id=@Id;

13> END;

14>

go

1>

2> INSERT INTO test_trigger_table(id, name) VALUES

(1,

'ABC');

3> go

BEFORE INSERT

New Name = ABC

(1

行受影响

)

1> select * from test_trigger_table;

2>

go

id

name

val

----------- ----------

-----------

1 ABC

100

(1

行受影响

)

UPDATE

1> CREATE

TRIGGER AfterUpdateTest

2>

ON test_trigger_table

3>

FOR

UPDATE

4> AS

5> DECLARE

6>

@OldName VARCHAR(10),

7>

@NewName VARCHAR(10);

8> BEGIN

9>

PRINT('AFTER

UPDATE');

10>

SELECT @NewName = name FROM inserted;

11>

SELECT

@OldName = name FROM deleted;

12>

PRINT('Old Name = ' +

@OldName);

13>

PRINT('New Name = ' + @NewName);

14> END;

15>

go

1>

2> UPDATE test_trigger_table SET name = 'XYZ'

WHERE id =

1;

3> go

AFTER UPDATE

Old Name = ABC

(1

行受影响

)

New Name = XYZDELETE

1>

CREATE TRIGGER AfterDeleteTest

2>

ON test_trigger_table

3>

FOR

DELETE

4> AS

5> DECLARE

6>

@OldName VARCHAR(10);

7>

BEGIN

8>

PRINT('AFTER DELETE');

9>

SELECT @OldName = name FROM

deleted;

10>

PRINT('Old Name = ' + @OldName);

11> END;

12>

go

1> DELETE FROM test_trigger_table WHERE id = 1;

2> go

AFTER

DELETE

(1

行受影响

)

Old Name = XYZINSERT/UPDATE/DELETE

行为判断

测试此处时，先删除前面的

3

个触发器

1>

CREATE TRIGGER AfterAllTest

2>

ON test_trigger_table

3>

FOR

INSERT,UPDATE,DELETE

4> AS

5> BEGIN

6>

PRINT('AFTER

ALL');

7>

IF EXISTS(SELECT 1 FROM inserted) AND NOT

EXISTS(SELECT 1 FROM

deleted)

8>

BEGIN

9>

PRINT('INSERTING');

10>

END;

11>

IF EXISTS(SELECT 1 FROM inserted) AND

EXISTS(SELECT 1 FROM

deleted)

12>

BEGIN

13>

PRINT('UPDATING');

14>

END;

15>

IF NOT EXISTS(SELECT 1 FROM inserted)

AND EXISTS(SELECT 1 FROM

deleted)

16>

BEGIN

17>

PRINT('DELETING');

18>

END;

19> END;

20> go

1> INSERT INTO test_trigger_table(id,

name) VALUES (1, 'ABC');

2> UPDATE test_trigger_table SET name = 'XYZ'

WHERE id = 1;

3> DELETE FROM test_trigger_table WHERE id = 1;

4>

go

AFTER ALL

INSERTING

(1

行受影响

)

AFTER ALL

UPDATING

AFTER ALL

DELETING

1>SQL Server

INSERT/UPDAT E/DELETE

多行

1> CREATE TRIGGER

AfterAllTest2

2>

ON test_trigger_table

3>

FOR

INSERT,UPDATE,DELETE

4> AS

5> DECLARE

6>

@InsertCount

INT,

7>

@DeleteCount INT;

8> BEGIN

9>

PRINT('AFTER ALL

2');

10>

SELECT @InsertCount = COUNT(1) FROM

inserted;

11>

SELECT @DeleteCount = COUNT(2) FROM deleted;

12>

PRINT('@InsertCount=' + STR(@InsertCount) );

13>

PRINT('@DeleteCount=' + STR(@DeleteCount) );

14> END;

15>

go

1> INSERT INTO test_trigger_table(id, name) VALUES

(1, 'ABC');

2>

go

AFTER ALL

INSERTING

AFTER ALL 2

@InsertCount=

1

(1

行受影响

)

@DeleteCount=

0

1> INSERT INTO

test_trigger_table(id, name) VALUES (2, 'DEF');

2> go

AFTER

ALL

INSERTING

AFTER ALL 2

@InsertCount=

1

(1

行受影响

)

@DeleteCount=

0

1> insert into test_trigger_table

select * from test_trigger_table;

2> go

AFTER ALL

INSERTING

AFTER

ALL 2

@InsertCount=

2

(2

行受影响

)

@DeleteCount=

0

1> UPDATE test_trigger_table SET

name = 'XYZ' WHERE id = 1;

2> go

AFTER ALL