[SQL Server] TRIGGER 触发器获得insert, delete, update行的信息

大家都知道SQL Server触发器分为DDL、DML和登录触发器。其中DML触发器对于一个表来说,又可分为Insert、Update、Delete三种触发器。也许触发器的功能作用大家都清楚了,可是大家知道当批量Update或者批量Delete数据的时候,触发器如何工作吗?在这种情况下,触发器不是逐条触发的(也就是说不是删除一条记录的同时,触发器开始工作一次),而是当批量数据操作完成后,只触发一次。但这次触发的数据是整个批量数据,而不是逐条数据。

 

一段trigger codes例子:

 


-- dbo.trGroups.SQL
   
IF EXISTS    (
    
SELECT * FROM sysobjects WHERE
        name 
= 'trGroups' AND type = 'TR')
BEGIN
    
DROP TRIGGER trGroups
    
IF NOT EXISTS    (
        
SELECT * FROM sysobjects WHERE
            name 
= 'trGroups' AND type = 'TR')
        
PRINT 'DROP trGroups SUCCEEDED'
    
ELSE
        
PRINT 'DROP trGroups FAILED'
END
ELSE
    
PRINT 'Attempting to CREATE trGroups'
GO

CREATE TRIGGER dbo.trGroups
    
ON groups
    AFTER 
INSERTUPDATEDELETE
AS

    
-- This trigger submits a cached rates delete job for modified groups.
    -- It also updates the groups' last modified columns for modified groups.
    -- It also updates the groups' state (if currently unspecified) based on 
    -- the (first) state extracted from groups' zip code for new/modified groups.
    -- No error checking is currently being performed.

    
SET NOCOUNT ON -- stop display of rowcount messages

    
DECLARE
        
@groupID        int,
        
@deletedCount    int,
        
@returnCode    int
    
    
SELECT 
        
@deletedCount    = COUNT(*)
    
FROM
        deleted 
WITH (NOLOCK)

    
IF @deletedCount > 0
    
BEGIN
        
DECLARE groupIDCursor CURSOR
        FAST_FORWARD
        
FOR
            
SELECT DISTINCT
                t1.groupID
            
FROM
            (
                
SELECT
                    groupID
                
FROM
                    inserted 
WITH (NOLOCK)

                
UNION

                
SELECT
                    groupID
                
FROM
                    deleted 
WITH (NOLOCK)
            ) 
as t1

        
OPEN groupIDCursor

        
FETCH NEXT
            
FROM
                groupIDCursor
            
INTO
                
@groupID

        
WHILE @@FETCH_STATUS = 0
            
BEGIN
                
EXEC @returnCode = dbo.spCachingModuleAddJobDeleteCachedRatesByGroupID
                    
@groupID = @groupID

                
FETCH NEXT
                    
FROM
                        groupIDCursor
                    
INTO
                        
@groupID
            
END
        
        
CLOSE groupIDCursor    
        
DEALLOCATE groupIDCursor
    
END


GO

IF EXISTS    (
    
SELECT * FROM sysobjects WHERE
        name 
= 'trGroups' AND type = 'TR')
    
PRINT 'CREATE trGroups SUCCEEDED'
ELSE
        
PRINT 'CREATE trGroups FAILED'
GO

 

 触发器语句中使用了两种特殊的表:deleted 表和 inserted 表。
Deleted 表用于存储 DELETE 和 UPDATE 语句所影响的行的复本。在执行 DELETE 或 UPDATE 语句时,行从触发器表中删除,并传输到 deleted 表中。Deleted 表和触发器表通常没有相同的行。

Inserted 表用于存储 INSERT 和 UPDATE 语句所影响的行的副本。在一个插入或更新事务处理中,新建行被同时添加到 inserted 表和触发器表中。Inserted 表中的行是触发器表中新行的副本。

1.插入操作(Insert)
Inserted表有数据,Deleted表无数据

2.删除操作(Delete)
Inserted表无数据,Deleted表有数据

3.更新操作(Update)
Inserted表有数据(新数据),Deleted表有数据(旧数据)[

 

 

INSTEAD OF 触发器用来代替通常的触发动作,即当对表进行INSERT、UPDATE 或 DELETE 操作时,系统不是直接对表执行这些操作,而是把操作内容交给触发器,让触发器检查所进行的操作是否正确。如正确才进行相应的操作。因此,INSTEAD OF 触发器的动作要早于表的约束处理。
INSTEAD OF 触发器的操作有点类似于完整性约束。在对数据库的操纵时,有些情况下使用约束可以达到更好的效果,而如果采用触发器,则能定义比完整性约束更加复杂的约束。有关触发器与约束的比较,请参见联机丛书。
INSTEAD OF 触发器不仅可在表上定义,还可在带有一个或多个基表的视图上定义,但在作为级联引用完整性约束目标的表上限制应用。
AFTER 触发器定义了对表执行了 INSERT、UPDATE 或 DELETE 语句操作之后再执行的操作。比如对某个表中的数据进行了更新操作后,要求立即对相关的表进行指定的操作,这时就可以采用 AFTER 触发器。AFTER 触发器只能在表上指定,且动作晚于约束处理。
每一个表上只能创建一个 INSTEAD OF 触发器,但可以创建多个 AFTER 触发器。

 

 

 

 

INSTEAD OF 触发器的主要优点是可以使不能更新的视图支持更新。基于多个基表的视图必须使用 INSTEAD OF 触发器来支持引用多个表中数据的插入、更新和删除操作。INSTEAD OF 触发器的另一个优点是使您得以编写这样的逻辑代码:在允许批处理的其他部分成功的同时拒绝批处理中的某些部分。

INSTEAD OF 触发器可以进行以下操作:

  • 忽略批处理中的某些部分。

  • 不处理批处理中的某些部分并记录有问题的行。

  • 如果遇到错误情况则采取备用操作。

    注意注意

    对于含有使用 DELETE 或 UPDATE 级联操作定义的外键的表,不能定义 INSTEAD OF DELETE 和 INSTEAD OF UPDATE 触发器。

将此逻辑作为 INSTEAD OF 触发器的一部分进行编码,可避免所有访问数据的应用程序必须重新实现该逻辑。

在下列 Transact-SQL 语句序列中,INSTEAD OF 触发器更新视图中的两个基表。另外,显示以下处理错误的方法:

  • 忽略对 Person 表的重复插入,并且插入的信息将记录在 PersonDuplicates 表中。

  • 将对 EmployeeTable 的重复插入转变为 UPDATE 语句,该语句将当前信息检索至 EmployeeTable,而不会产生重复键冲突。

Transact-SQL 语句创建两个基表、一个视图、一个记录错误表和视图上的 INSTEAD OF 触发器。以下表将个人数据和业务数据分开并且是视图的基表。

 
复制
CREATE TABLE Person
   (
    SSN         char(11) PRIMARY KEY,
    Name        nvarchar(100),
    Address     nvarchar(100),
    Birthdate   datetime
   )

CREATE TABLE EmployeeTable
   (
    EmployeeID       int PRIMARY KEY,
    SSN              char(11) UNIQUE,
    Department       nvarchar(10),
    Salary           money,
    CONSTRAINT FKEmpPer FOREIGN KEY (SSN)
    REFERENCES Person (SSN)
   )

下面的视图使用某个人的两个表中的所有相关数据建立报表。

 
复制
CREATE VIEW Employee AS
SELECT P.SSN as SSN, Name, Address,
       Birthdate, EmployeeID, Department, Salary
FROM Person P, EmployeeTable E
WHERE P.SSN = E.SSN

可记录对插入具有重复的社会保障号的行的尝试。PersonDuplicates 表记录插入的值、尝试插入的用户的姓名和插入的时间。

 
复制
CREATE TABLE PersonDuplicates
   (
    SSN           char(11),
    Name          nvarchar(100),
    Address       nvarchar(100),
    Birthdate     datetime,
    InsertSNAME   nchar(100),
    WhenInserted  datetime
   )

INSTEAD OF 触发器将行插入到单个视图的多个基表中。在 PersonDuplicates 表中记录了插入具有重复社会保障号的行的尝试。EmployeeTable 中的重复行将更改为更新语句。

 
复制
CREATE TRIGGER IO_Trig_INS_Employee ON Employee
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
-- Check for duplicate Person. If there is no duplicate, do an insert.
IF (NOT EXISTS (SELECT P.SSN
      FROM Person P, inserted I
      WHERE P.SSN = I.SSN))
   INSERT INTO Person
      SELECT SSN,Name,Address,Birthdate
      FROM inserted
ELSE
-- Log an attempt to insert duplicate Person row in PersonDuplicates table.
   INSERT INTO PersonDuplicates
      SELECT SSN,Name,Address,Birthdate,SUSER_SNAME(),GETDATE()
      FROM inserted
-- Check for duplicate Employee. If no there is duplicate, do an INSERT.
IF (NOT EXISTS (SELECT E.SSN
      FROM EmployeeTable E, inserted
      WHERE E.SSN = inserted.SSN))
   INSERT INTO EmployeeTable
      SELECT EmployeeID,SSN, Department, Salary
      FROM inserted
ELSE
--If there is a duplicate, change to UPDATE so that there will not
--be a duplicate key violation error.
   UPDATE EmployeeTable
      SET EmployeeID = I.EmployeeID,
          Department = I.Department,
          Salary = I.Salary
   FROM EmployeeTable E, inserted I
   WHERE E.SSN = I.SSN
END
posted @ 2012-05-31 20:54  星火卓越  阅读(1911)  评论(0编辑  收藏  举报