SQL Server触发器总结

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：

select @var1=col,@var2=col2 from inserted  
当受影响行为0，@var1,@var2为空

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

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

语句2：

set @var1=(select col from inserted)  

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

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

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

正确的做法：

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

-- Create table T1  
SET NOCOUNT ON;  
USE tempdb;  
GO  
IF OBJECT_ID('dbo.T1') IS NOT NULL  
  DROP TABLE dbo.T1;  
GO  
  
CREATE TABLE dbo.T1  
(  
  keycol  INT         NOT NULL PRIMARY KEY,  
  datacol VARCHAR(10) NOT NULL  
);  
GO  
-- Creation Script for trg_T1_i Trigger  
CREATE TRIGGER trg_T1_i ON T1 FOR INSERT  
AS  
  
DECLARE @rc AS INT;  
SET @rc = @@rowcount;  
  
IF @rc = 0 RETURN;  
  
DECLARE @keycol AS INT, @datacol AS VARCHAR(10);  
  
IF @rc = 1 -- single row  
BEGIN  
  SELECT @keycol = keycol, @datacol = datacol FROM inserted;  
  PRINT 'Handling keycol: '  
    + CAST(@keycol AS VARCHAR(10))  
    + ', datacol: ' + @datacol;  
END  
ELSE -- multi row  
BEGIN  
  SELECT * INTO #I FROM inserted;  
  CREATE UNIQUE CLUSTERED INDEX idx_keycol ON #I(keycol);  
  
  SELECT @keycol = keycol, @datacol = datacol  
  FROM (SELECT TOP (1) keycol, datacol  
        FROM #I  
        ORDER BY keycol) AS D;  
  
  WHILE @@rowcount > 0  
  BEGIN  
    PRINT 'Handling keycol: '  
      + CAST(@keycol AS VARCHAR(10))  
      + ', datacol: ' + @datacol;  
  
    SELECT @keycol = keycol, @datacol = datacol  
    FROM (SELECT TOP (1) keycol, datacol  
          FROM #I  
          WHERE keycol > @keycol  
          ORDER BY keycol) AS D;  
  END  
END  
GO  
-- Test trg_T1_i trigger  
  
-- 0 Rows  
INSERT INTO dbo.T1 SELECT 1, 'A' WHERE 1 = 0;  
GO  
  
-- 1 Row  
INSERT INTO dbo.T1 SELECT 1, 'A';  
  
-- Multi Rows  
INSERT INTO dbo.T1  
  SELECT 2, 'B'  
  UNION ALL  
  SELECT 3, 'C'  
  UNION ALL  
  SELECT 4, 'D';  
GO  
-- Cleanup  
IF OBJECT_ID('dbo.T1') IS NOT NULL  
  DROP TABLE dbo.T1;  
GO