SQL Server 触发器

最新推荐文章于 2023-05-30 06:45:00 发布

jueng

最新推荐文章于 2023-05-30 06:45:00 发布

阅读量1k

点赞数 1

分类专栏： sqlserver

本文链接：https://blog.csdn.net/sssix/article/details/14162381

版权

sqlserver 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

ctreate database tttt;  CREATE TABLE    ;    CREATE TRIGGER  ; create cursor;  create  procedure;   
use tttt;
create table xxxx.............

 
 
  
  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 ;
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 实现在触发以后，判断哪些列被更新了
 
 
.