|
测试表
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 = XYZ
DELETE
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 = XYZ
INSERT/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/UPDATE/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
UPDATING
AFTER ALL
2
@InsertCount= 2
(2 行受影响)
@DeleteCount= 2
1>
针对特定列的触发
测试此处时，先删除其它的触发器
SQL Server
通过
COLUMNS_UPDATED
来判断哪些列被更新
COLUMNS_UPDATED
函数以从左到右的顺序返回位，最左边的为最不重要的位。
最左边的位表示表中的第一列；向右的下一位表示第二列，依此类推。
如果在表上创建的触发器包含 8
列以上，则 COLUMNS_UPDATED 返回多个字节，最左边的为最不重要的字节。
在 INSERT 操作中 COLUMNS_UPDATED
将对所有列返回 TRUE 值，因为这些列插入了显式值或隐性 (NULL) 值。
1> drop TRIGGER AfterUpdateTest;
2> go
1> CREATE TRIGGER
AfterUpdateTest
2> ON test_trigger_table
3> FOR INSERT,
UPDATE
4> AS
5> DECLARE
6> @OldVal VARCHAR(10),
7>
@NewVal VARCHAR(10);
8> BEGIN
9>
10> IF (COLUMNS_UPDATED()
& 4 ) > 0
11> BEGIN
12> PRINT('AFTER UPDATE Only
Val');
13> SELECT @NewVal = val FROM inserted;
14> SELECT
@OldVal = val FROM deleted;
15> PRINT('Old Val = ' +
@OldVal);
16> PRINT('New Val = ' + @NewVal);
17>
END;
18> END
19> go
1> INSERT INTO test_trigger_table(id,
name, val) VALUES (1, 'ABC', 1);
2> go
AFTER UPDATE Only Val
(1 行受影响)
New Val = 1
1>
2> UPDATE test_trigger_table SET
name = 'XYZ' WHERE id=1;
3> go
(1 行受影响)
1>
2> UPDATE test_trigger_table SET val = 20 WHERE
id=1;
3> go
AFTER UPDATE Only Val
Old Val = 1
(1 行受影响)
New Val = 20
已创建的触发器的查询
1>
select
2> name
3> from
4> sysobjects
5>
where
6> xtype='TR'
7> and
parent_obj=object_id('table_1')
8> go
name
--------------------------------------------------------------------------------
------------------------------------------------
AfterInsertTable_1
(1 行受影响)
DDL触发器
数据库级别
1> CREATE TRIGGER CretaeTableTrigger
2> ON
DATABASE
3> FOR CREATE_TABLE
4> AS
5> BEGIN
6> DECLARE
@EventData AS xml;
7>
8> DECLARE @EventType AS
varchar(100);
9> DECLARE @PostTime AS varchar(100);
10>
DECLARE @ServerName AS varchar(100);
11> DECLARE @LoginName AS
varchar(100);
12> DECLARE @DatabaseName AS varchar(100);
13>
DECLARE @CommandText AS varchar(100);
14>
15> SET @EventData =
EVENTDATA();
16>
17> SELECT
18> @EventType =
t.c.value('(EventType)[1]', 'varchar(100)' ),
19> @PostTime =
t.c.value('(PostTime)[1]', 'varchar(100)' ),
20> @ServerName =
t.c.value('(ServerName)[1]', 'varchar(100)' ),
21> @LoginName =
t.c.value('(LoginName)[1]', 'varchar(100)' ),
22> @DatabaseName =
t.c.value('(DatabaseName)[1]', 'varchar(100)' ),
23> @CommandText =
t.c.value('(TSQLCommand/CommandText)[1]', 'varchar(100
)' )
24>
FROM
25> @EventData.nodes('/EVENT_INSTANCE')
t(c);
26>
27> PRINT( 'CREATE_TABLE' );
28> PRINT(
@EventType );
29> PRINT( @PostTime );
30> PRINT( @ServerName
);
31> PRINT( @LoginName );
32> PRINT( @DatabaseName
);
33> PRINT( @CommandText );
34> END
35> go
1> create
table yyy (a INT);
2>
go
CREATE_TABLE
CREATE_TABLE
2010-10-01T21:32:24.843
HOME-BED592453C\SQLEXPRESS
HOME-BED592453C\wzq
Stock
create
table yyy (a INT);
DDL触发器 服务器级别
1>
CREATE TRIGGER ddl_trig_database
2> ON ALL SERVER
3> FOR
CREATE_DATABASE
4> AS
5> BEGIN
6> DECLARE @EventData AS
xml;
7> DECLARE @DatabaseName AS varchar(100);
8> DECLARE
@Sql NVARCHAR(200);
9>
10> SET @EventData =
EVENTDATA();
11>
12> SELECT
13> @DatabaseName =
t.c.value('(DatabaseName)[1]', 'varchar(100)' )
14> FROM
15>
@EventData.nodes('/EVENT_INSTANCE') t(c);
16>
17> PRINT 'Database
Created.'
18>
19> SET @Sql =
20> 'CREATE TABLE ' +
@DatabaseName + '.dbo.abc( a INT )' ;
21> PRINT @Sql;
22> EXEC
(@Sql);
23>
24> END
25> GO
1> create database
test2
2> go
Database Created.
CREATE TABLE test2.dbo.abc( a INT
)
1> use test2
2> go
已将数据库上下文更改为 'test2'。
1> select * from
abc
2> go
a
-----------
(0 行受影响)
小结
触发器只有
语句级别的
只有 AFTER
取得 操作数据，通过 inserted deleted
编写触发器的时候，要充分考虑 一次更新一条，与一次更新多条的问题。
避免简单的 SELECT @NewName = name FROM
inserted; 代码操作。
对于 FOR INSERT,UPDATE,DELETE 的
通过 查询 inserted 与 deleted 的记录 来判断
通过COLUMNS_UPDATED 实现在触发以后，判断哪些列被更新了。
|
