SQLServer触发器：AFTER、INSTEADOF及审计应用详解

# SQL Server触发器：AFTER、INSTEAD OF及审计应用详解 ## 1. SQL查询与触发器基础 ### 1.1 初始查询示例 首先来看一些基础的SQL查询语句： ```sql SELECT 'Second' FROM sysobjects WHERE type = 'S' SELECT @@IDENTITY AS 'IDENTITY', SCOPE_IDENTITY () AS 'SCOPE IDENTITY', IDENT_CURRENT ('Summary') AS 'Summary', IDENT_CURRENT ('Raw') AS 'Raw' ``` 第一个`INSERT`语句会向`Raw`表插入若干行，每行对应`sysobjects`中的一个用户表，`RawData`列会被填充为常量`'First'`。这会触发`tri_Raw`触发器，向`Summary`表插入一行数据，此时`@@IDENTITY`的值为1。而`SCOPE_IDENTITY()`返回的值为15，这是因为在Northwind数据库中有15个用户表。两个`IDENT_CURRENT()`调用返回的值分别对应`@@IDENTITY`和`SCOPE_IDENTITY()`。 第二个`INSERT`语句与第一个类似，不过这次是为数据库中的每个系统表插入一行，`RawData`列填充为常量`'Second'`。由于最后填充的表是`Summary`，且该表现在只有两行，所以`@@IDENTITY`的值为2，`SCOPE_IDENTITY()`的值增加了约19（在Northwind数据库中）。 ### 1.2 sysobjects表说明 `sysobjects`是每个数据库中都存在的系统表，它列出了数据库中每个对象的元数据信息，如名称、类型等。其中，类型`U`表示用户表，类型`S`表示系统表。 ## 2. AFTER触发器理解 ### 2.1 AFTER触发器概述 在SQL Server 2000版本发布之前，唯一的触发器类型是`FOR`触发器。这种触发器会在所有约束（如果有的话）检查之后触发。如果存在约束违规，约束会阻止触发器触发，从而防止级联删除。在SQL Server 2000中，使用`AFTER`触发器，指定`AFTER`与早期版本指定`FOR`的效果相同，名称的改变更直观，反映了触发器在表的数据修改操作之后触发。 与`INSTEAD OF`触发器不同，不允许在视图上创建`AFTER`触发器。SQL Server 7.0和2000版本的一个重要区别是，2000版本通过DRI支持级联删除，级联删除可以发生，并且允许`DELETE`触发器触发。需要记住的基本规则是，级联中的所有约束必须成功，触发器才能触发。 ### 2.2 AFTER触发器应用示例 #### 2.2.1 处理datetime列 以下是一个使用`AFTER`触发器从`datetime`列中移除时间部分的示例： ```sql CREATE TABLE DateTable ( ID int NOT NULL IDENTITY (1, 1) PRIMARY KEY, Txt char (10) NOT NULL, EntryDate datetime NOT NULL ) GO CREATE TRIGGER triu_DateTable ON DateTable AFTER INSERT, UPDATE AS IF @@ROWCOUNT = 0 RETURN IF UPDATE (EntryDate) UPDATE D SET EntryDate = CONVERT (char (10), I.EntryDate, 112) FROM inserted I JOIN DateTable D ON D.ID = I.ID GO ``` 对于`DateTable`表的每次`INSERT`或`UPDATE`操作，`triu_DateTable`触发器都会触发。如果没有行被修改，触发器会立即返回。然后，它会判断`EntryDate`列是否被更新，如果是更新操作或者触发触发器的操作是`INSERT`，则触发器会将`EntryDate`重置，去除时间部分，将时间设置为午夜。 也可以在`DateTable`表的`EntryDate`列上应用`CHECK`约束，防止`EntryDate`列包含除午夜之外的时间，这样就不需要触发器了。可以将约束看作是主动的，而触发器是被动的。约束的问题在于，所有客户端代码都必须进行清理，以确保没有带时间组件的日期传递到SQL Server。而使用触发器，客户端代码则无关紧要，因为数据在插入数据库之前会被清理。 #### 2.2.2 级联删除示例 考虑以下创建`Parent`、`Child`和`Grandchild`表的脚本： ```sql CREATE TABLE Parent ( ID int NOT NULL PRIMARY KEY ) GO CREATE TABLE Child ( ID int NOT NULL PRIMARY KEY REFERENCES Parent (ID) ON DELETE CASCADE ) GO CREATE TABLE GrandChild ( ID int NOT NULL PRIMARY KEY REFERENCES Child (ID) ON DELETE CASCADE ) GO INSERT Parent VALUES (1) INSERT Parent VALUES (2) INSERT Parent VALUES (3) INSERT Parent VALUES (4) GO INSERT Child VALUES (1) INSERT Child VALUES (2) INSERT Child VALUES (3) INSERT Child VALUES (4) GO INSERT GrandChild VALUES (1) INSERT GrandChild VALUES (2) INSERT GrandChild VALUES (3) INSERT GrandChild VALUES (4) GO ``` 每个表都使用了`ON DELETE CASCADE`选项。接下来，为每个表创建`DELETE`触发器： ```sql CREATE TRIGGER trd_Parent ON Parent AFTER DELETE AS IF @@ROWCOUNT = 0 RETURN PRINT 'Inside Parent trigger.' GO CREATE TRIGGER trd_Child ON Child AFTER DELETE AS IF @@ROWCOUNT = 0 RETURN PRINT 'Inside Child trigger.' GO CREATE TRIGGER trd_GrandChild ON GrandChild AFTER DELETE AS IF @@ROWCOUNT = 0 RETURN PRINT 'Inside GrandChild trigger.' GO ``` 执行以下删除语句： ```sql DELETE Parent WHERE ID BETWEEN 2 AND 3 ``` 会得到按相反顺序的`PRINT`消息，即先从`tri_GrandChild`，然后是`tri_Child`，最后是`tri_Parent`。在编写级联情况下的触发器时，需要牢记这一点。 ## 3. INSTEAD OF触发器理解 ### 3.1 INSTEAD OF触发器概述 `INSTEAD OF`触发器是SQL Server 2000新增的功能，它允许用自己的功能替换标准的`INSERT`、`UPDATE`或`DE