SQL Server触发器总结

触发器的基本概念

触发器的定义：

触发器可以看做由数据库服务器事件自动引发的程序。

触发器与存储过程：

在SQL Server内部，触发器可以看做是存储过程。但触发器没有接口（输入参数和输出参数），而且不能被显示调用。

触发器与事务:

触发器是引发它们事务的一部分。如果触发器回滚分两种情况：在显式事务中将撤销从最外层的begin tran开始的所有操作。如果不在显式事务中，仅撤销触发器内部的所有操作。

 

触发器分类

AFTER触发器：自动响应用户或应用程序提交的语句

INSTEAD OF触发器：用自己的代码替换原语句

---

DML触发器：响应INSERT,UPDATE,DELETE语句。注意没有响应SELECT语句的触发器

DLL触发器：响应CREATE,ALTER,DROP语句

---

使用T-SQL或.net CLR开发的触发器

 

AFTER触发器

AFTER触发器在触发语句执行后触发。使用该触发器可以响应对数据库服务器的更改。

该触发器只能在持久表上创建，不能试图和临时表上创建。

AFTER触发器按语句触发，无论触发语句影响多少行，只触发一次。

一个语句可创建多个触发器。同一表上，同一类型语句的多个触发器按顺序执行，可以使用sp_settriggerorder指定顺序。

inserted和deleted表

在DML触发器中可使用deleted和inserted表访问受影响行的新旧镜像。

Deleted表包含受影响行的旧镜像。在Update和Delete触发器中包含数据。Insert触发器为空

Inserted表包含受影响行的新镜像。在Inert和Update触发器中包含数据，Delete触发器为空。

Inserted和Deleted表结构和触发器所在的表结构相同，但没有索引。

在SQL Server2005前，Inserted和Deleted表是事务日志的视图来实现。

在SQL Server2005中，Inserted和Deleted指向tempdb中的行版本数据。

触发器的性能问题

1.Inserted和Deleted表没有索引，对它们的查询会导致表扫描。

2.SQL Server2000会增加事务日志的压力。

3.SQL Server2000增加tempdb的压力。

触发器应该根据不同受影响的行数做不同的响应

触发器按语句触发，无论触发语句影响多少行，只触发一次

语句1：

 

1. select  @var1=col,@var2=col2  from  inserted  

当受影响行为0，@var1,@var2为空

当受影响行为1，@var1,@var2该行相应列的值

当受影响行为多行，@var1,@var2为最后一行的相应列的值

语句2：

 

1. set  @var1=( select  col  from  inserted)  

当受影响行为0，@var1为空

当受影响行为1，@var1该行相应列的值

当受影响行为多行，出错。

正确的做法：

触发器应该根据不同受影响的行数做不同的响应：0行直接返回，1行取相应列的值，多行则使用联合查询。

 

1. -- Create table T1   
2. SET  NOCOUNT  ON ;  
3. USE tempdb;  
4. GO  
5. IF OBJECT_ID('dbo.T1' )  IS   NOT   NULL   
6.   DROP   TABLE  dbo.T1;  
7. GO  
8.   
9. CREATE   TABLE  dbo.T1  
10. (  
11.   keycol  INT           NOT   NULL   PRIMARY   KEY ,  
12.   datacol VARCHAR (10)  NOT   NULL   
13. );  
14. GO  

1. -- Creation Script for trg_T1_i Trigger   
2. CREATE   TRIGGER  trg_T1_i  ON  T1  FOR   INSERT   
3. AS   
4.   
5. DECLARE  @rc  AS   INT ;  
6. SET  @rc = @@rowcount;  
7.   
8. IF @rc = 0 RETURN ;  
9.   
10. DECLARE  @keycol  AS   INT , @datacol  AS   VARCHAR (10);  
11.   
12. IF @rc = 1 -- single row   
13. BEGIN   
14.   SELECT  @keycol = keycol, @datacol = datacol  FROM  inserted;  
15.   PRINT 'Handling keycol: '   
16.     + CAST (@keycol  AS   VARCHAR (10))  
17.     + ', datacol: '  + @datacol;  
18. END   
19. ELSE   -- multi row   
20. BEGIN   
21.   SELECT  *  INTO  #I  FROM  inserted;  
22.   CREATE   UNIQUE  CLUSTERED  INDEX  idx_keycol  ON  #I(keycol);  
23.   
24.   SELECT  @keycol = keycol, @datacol = datacol  
25.   FROM  ( SELECT   TOP  (1) keycol, datacol  
26.         FROM  #I  
27.         ORDER   BY  keycol)  AS  D;  
28.   
29.   WHILE @@rowcount > 0  
30.   BEGIN   
31.     PRINT 'Handling keycol: '   
32.       + CAST (@keycol  AS   VARCHAR (10))  
33.       + ', datacol: '  + @datacol;  
34.   
35.     SELECT  @keycol = keycol, @datacol = datacol  
36.     FROM  ( SELECT   TOP  (1) keycol, datacol  
37.           FROM  #I  
38.           WHERE  keycol > @keycol  
39.           ORDER   BY  keycol)  AS  D;  
40.   END   
41. END   
42. GO  

1. -- Test trg_T1_i trigger   
2.   
3. -- 0 Rows   
4. INSERT   INTO  dbo.T1  SELECT  1,  'A'   WHERE  1 = 0;  
5. GO  
6.   
7. -- 1 Row   
8. INSERT   INTO  dbo.T1  SELECT  1,  'A' ;  
9.   
10. -- Multi Rows   
11. INSERT   INTO  dbo.T1  
12.   SELECT  2,  'B'   
13.   UNION   ALL   
14.   SELECT  3,  'C'   
15.   UNION   ALL   
16.   SELECT  4,  'D' ;  
17. GO  

1. -- Cleanup   
2. IF OBJECT_ID('dbo.T1' )  IS   NOT   NULL   
3.   DROP   TABLE  dbo.T1;  
4. GO