Sql Server系列：触发器

　　触发器的一些常见用途：

　　◊ 强制参照完整性

　　◊ 常见审计跟踪（Audit Trails）：这意味着写出的记录不仅跟踪大多数当前的数据，还包括对每个记录进行实际修改的历史数据。

　　◊ 创建与CHECK约束类似的功能：与CHECK约束不同，这种功能可以跨表、跨数据库甚至是跨服务器使用。

　　◊ 用自己的语句代替用户的操作语句：常用于启动复杂视图的插入操作。

1 触发器的概念

　　触发器是一种特殊类型的存储过程，对特定事件作出响应。触发器有两种类型：数据定义语言触发器和数据操作语言触发器。

　　数据定义语言（Data Definition Language，DDL）触发器在用户以某些方式（CERATE、ALTER、DROP）对数据库结构进行修改时激活而作出响应。在对数据库结构的改变或历史进行极为严格的审计时才会使用DDL触发器。

　　数据操作语言（Data Manipulation Language，DML）触发器是一些附加在特定表或视图上的代码片段。与需要显示调用的存储过程不同，只要有附加触发器的事件在表中发生，触发器中的代码就好自动运行。实际上也不能显式调用触发器，唯一的做法是在指定的表中执行所需的操作。

2 数据操作语言触发器

　　数据操作语言（DML）触发器是一些附加在特定表或视图上的操作代码，当数据库服务器中发生数据操作语言事件时执行执行操作。

　　SQL Server中数据操作语言触发器的类型：

　　◊ INSERT触发器

　　◊ DELETE触发器

　　◊ UPDATE触发器

　　◊ 以上任意类型的混合

　　可以考虑使用DML触发器的情形：

　　◊ 通过数据库中的相关表实现级联更改。

　　◊ 防止恶意或者错误的INSERT、UPDATE和DELETE操作，并强制执行比CHECK约束定义更为复杂的其他限制。

　　◊ 评估数据修改前后表的状态，并根据该差异采取措施。

　　在SQL Server 中，针对每个DML触发器定义了两个特殊的表：DELETED表和INSERTED表，这个两个逻辑表在内存中存放，由系统来创建和维护，用户不能对它们进行修改。触发器执行完成之后与该触发器相关的这两个表也会被删除。

　　◊ DELETED表存放执行DELETE或UPDATE语句而要从表中删除的所有行。在执行DELETE或UPDATE时，被删除的行从触发触发器的表中被移动到DELETED表，这两个表值会有公共的行。

　　◊ INSERTED表存放执行INSERT或UPDATE语句而向表中插入的所有行，在执行INSERT或UPDATE事务中，新行同时添加到触发器的表和INSERTED表。INSERTED表的内容是触发触发器的表中新行的副本，即INSERTED表中的行总是与作用表中的新行相同。

2.1 创建DML触发器语法

CREATE TRIGGER [ schema_name . ]trigger_name 
ON { table | view } 
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF } 
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 
[ WITH APPEND ]
[ NOT FOR REPLICATION ] 
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }

<dml_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

<method_specifier> ::= 
    assembly_name.class_name.method_name

　　其中，参数说明：

　　◊ FOR|AFTER：用于指定触发器只有在触发SQL语句中指定的所有操作都已成功执行之后才激发。所有的引用级联操作和约束检查也必须在激发此触发器之前成功完成。

　　　　　　　　如果仅指定 FOR 关键字，则 AFTER 为默认值。
　　　　　　　　不能对视图定义 AFTER 触发器。

　　◊ INSTEAD OF：指定执行 DML 触发器而不是触发 SQL 语句，因此，其优先级高于触发语句的操作。不能为 DDL 或登录触发器指定 INSTEAD OF。

　　　　　　　　　　对于表或视图，每个 INSERT、UPDATE 或 DELETE 语句最多可定义一个 INSTEAD OF 触发器。但是，可以为具有自己的 INSTEAD OF 触发器的多个视图定义视图。
　　　　　　　　　　 INSTEAD OF 触发器不可以用于使用 WITH CHECK OPTION 的可更新视图。如果将 INSTEAD OF 触发器添加到指定了 WITH CHECK OPTION 的可更新视图中，则 SQL Server 将引发错误。用户须用 ALTER VIEW 删除该选项后才能定义 INSTEAD OF 触发器。

2.2 INSERT触发器

　　当用户向表中插入新的记录行时，被标记为FOR INSERT的触发器的代码就会执行。SQL Server会创建一个新行的副本，将副本插入到一个特殊的表INSERTED中。该表只在触发器作用域内存在。

　　示例：

CREATE TRIGGER TRG_Category_Insert
ON [dbo].[Category]
FOR INSERT
AS
    BEGIN
        IF OBJECT_ID(N'Log', N'U') IS NULL
            CREATE TABLE [Log]
            (
                LogID INT IDENTITY(1,1) PRIMARY KEY,
                TableName VARCHAR(50) NOT NULL,
                Records INT NULL,
                Remark VARCHAR(1000) NULL
            )

        DECLARE @CategoryID INT
        DECLARE @CategoryName VARCHAR(50)

        SELECT @CategoryID = CategoryID, @CategoryName = CategoryName
        FROM INSERTED

        DECLARE @Records INT
        SELECT @Records = COUNT(1) FROM dbo.Category

        INSERT INTO [Log]
            (TableName, Records, Remark)
        VALUES 
            ('Category', CONVERT(VARCHAR, @Records), '插入一条新记录，CategoryID：' + CONVERT(VARCHAR, @CategoryID) + '，CategoryName：' + @CategoryName)
    END
GO

　　执行一条Insert SQL语句：

INSERT INTO dbo.Category(CategoryName) VALUES ('LINQ to SQL')

　　在某些情况下，根据数据库设计需要，可能会禁止用户对某些表的操作，可能在表上指定拒绝执行插入操作。

CREATE TRIGGER TRG_Category_Insert_Forbidden
    ON [Category]
    AFTER INSERT
AS
    BEGIN
        RAISERROR('Category表不允许插入数据，操作已禁止！', 1, 1);
        ROLLBACK TRANSACTION
    END

2.3 DELETE触发器

　　用户在执行DELETE操作时，会激活DELETE触发器，从而控制用户能够从数据库中删除的数据记录。触发DELETE触发器之后，用户删除的记录行会被添加到DELETED表中，原来表中的相应记录被删除，所以可以再DELETED表中查看删除的记录。

　　示例：

CREATE TRIGGER TRG_Category_Delete
ON [dbo].[Category]
AFTER DELETE
AS
    BEGIN
        SELECT CategoryID AS '删除的CategoryID', CategoryName AS '删除的CategoryName'
        FROM DELETED
    END
GO

　　执行一条删除记录SQL语句的结果：

2.4 UPDATE触发器

　　UPDATE触发器是在指定表上执行UPDATE语句时被调用。这种类型的触发器用来约束用户对现有数据的修改。

　　UPDATE触发器可以执行两种操作：更新前的记录存储到DELETED表，更新后的记录存储到INSERTED表。

　　示例：

CREATE TRIGGER TRG_Category_Update
ON [Category]
AFTER UPDATE
AS
    BEGIN
        SELECT CategoryID AS '更新前CategoryID', CategoryName AS '更新前CategoryName' FROM DELETED
        SELECT CategoryID AS '更新后CategoryID', CategoryName AS '更新后CategoryName' FROM INSERTED
    END
GO

　　执行一条Update SQL语句结果：

2.5 替代触发器

　　前面的3种触发器，INSERT、UPDATE、DELETE触发器，SQL Server服务器在执行触发AFTER触发器的SQL代码后，先建立临时的INSERTED和DELETED表，然后执行SQL代码中对数据的操作，最后才激活触发器中的代码。

　　替代（INSTEAD OF）触发器，SQL Server服务器在执行触发INSTEAD OF触发器的代码时，先建立临时的INSERTED和DELETED表，然后直接触发INSTEAD OF触发器，而拒绝执行用户输入的DML操作语句。

　　基于多个表的视图必须使用INSTEAD OF触发器来对多个表中的数据进行插入、更新和删除操作。

　　示例：

CREATE TRIGGER TRG_Product_Insert
    ON [Product]
    INSTEAD OF INSERT
AS
    BEGIN
        DECLARE @UnitPrice DECIMAL(18,2)
        SELECT @UnitPrice = UnitPrice FROM INSERTED

        IF @UnitPrice > 100
            RAISERROR('UnitPrice不能大于100，用户操作已禁止！', 1, 1)
    END

　　执行测试Insert SQL语句：

　　尽管执行Insert SQL语句之后，返回1行受影响，但由于是INSTEAD OF触发器，DML操作的记录并未插入到Product表中。

3 数据定义语言触发器

　　DDL触发器可以通过用户的操作而激活，在用户对数据库对象的创建修改和删除时触发。

3.1 创建DDL触发器

　　创建DDL触发器语法：

-- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)

CREATE TRIGGER [ schema_name . ]trigger_name 
ON { table | view } 
 [ WITH <dml_trigger_option> [ ,...n ] ] 
{ FOR | AFTER | INSTEAD OF } 
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 
  AS { sql_statement  [ ; ] [ ,...n ] [ ; ] > }

<dml_trigger_option> ::= 
        [ EXECUTE AS Clause ]

　　示例：

USE Portal
GO

CREATE TRIGGER TRG_Product_DenyAlterAndDrop
ON DATABASE
FOR ALTER_TABLE,DROP_TABLE
AS
    BEGIN
        RAISERROR('用户没有权限修改、删除数据表', 1, 1)
        ROLLBACK TRANSACTION
    END
GO

　　SQL执行删除表Product的ProductName列结果：

4 查看触发器

4.1 查看数据库触发器

4.2 查看表触发器

　　使用T-SQL查看：

EXEC sp_helptext TRG_Category_Insert

5 修改触发器

　　修改触发器语法：

ALTER TRIGGER schema_name.trigger_name 
ON  ( table | view ) 
[ WITH <dml_trigger_option> [ ,...n ] ]
 ( FOR | AFTER | INSTEAD OF ) 
{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] } 
[ NOT FOR REPLICATION ] 
AS { sql_statement [ ; ] [ ...n ] | EXTERNAL NAME <method specifier> 
[ ; ] }

6 删除触发器

　　DROP TRIGGER可以一次删除一个或多个触发器，其语法：

DROP TRIGGER [schema_name.]trigger_name [ ,...n ] [ ; ]

　　示例：

　　删除表触发器

DROP TRIGGER TRG_Category_Insert

　　删除数据库触发器

DROP TRIGGER TRG_Product_DenyAlterAndDrop ON DATABASE

7 启用/禁用触发器

7.1 禁用触发器

　　禁用数据库触发器，ON后面表示作用域

DISABLE TRIGGER [TRG_Product_DenyAlterAndDrop] ON DATABASE

　　禁用表触发器

ALTER TABLE [dbo].[Category] DISABLE TRIGGER [TRG_Category_Insert]

　　或

DISABLE TRIGGER [TRG_Category_Insert] ON [dbo].[Category]

7.2 启用触发器

　　启用数据库触发器

ENABLE TRIGGER [TRG_Product_DenyAlterAndDrop] ON DATABASE

　　启用表触发器

ALTER TABLE [dbo].[Category] ENABLE TRIGGER [TRG_Category_Insert]

　　或

ENABLE TRIGGER [TRG_Category_Insert] ON [dbo].[Category]