SQL Server 2005的DML触发器

一、SQL Server 2005的DML触发器分为两类：
1. After触发器：这类触发器是在记录已经改变完之后（after），才会被激活执行，
        它主要是用于记录变更后的处理或检查，一旦发现错误，也可以用Rollback Transaction语句来回滚本次的操作。

2. Instead Of触发器：这类触发器一般是用来取代原本的操作，在记录变更之前发生的，它并不去执行原来SQL语句里的操作（Insert、Update、Delete），而去执行触发器本身所定义的操作。

      在SQL Server 2005里，为每个DML触发器都定义了两个特殊的表，一个是插入表(inserted)，一个是删除表(deleted)。
      这两个表是建在数据库服务器的内存中的，是由系统管理的逻辑表，而不是真正存储 在数据库中的物理表。对于这两个表，用户只有读取的权限，没有修改的权限。
      这两个表的结构与触发器所在数据表的结构是完全一致的，当触发器的工作完成之后，这两个表也将会从内存中删除。
      插入表(inserted)里存放的是更新前的记录：对于插入记录操作来说，插入表里存放的是要插入的数据；对于更新记录操作来说，插入表里存放的是要更新的记录。
      删除表(deldeted)里存放的是更新后的记录：对于更新记录操作来说，删除表里存放的是更新前的记录（更新完后即被删除）；对于删除记录操作来说，删除表里存入的是被删除的旧记录。

二、触发器的工作原理

1.  After触发器的工作原理
    After触发器是在记录更变完成之后(如果是在存储过程,而且有事务 ,则要在事务提交之后), 才会被激活并执行的。

    以删除记录为例,分为以下步骤：

    1)当SQL Server接收到一个要执行删除操作的SQL语句时，SQL Server先将要删除的记录存放在删除表里，

    2)把数据表里的记录删除，

    3)激活After触发器，执行After触发器里的SQL语句。


    4) 触发器执行完毕之后，删除内存中的删除表，退出整个操作。
    还是举上面的例子：在产品库存表里，如果要删除一条产品记录，在删除记录时，触发器可以检查该产品库存数量是否为零，如果不为零则取消删除操作。看一下数据库是怎么操作的：
    （1）接收SQL语句，将要从产品库存表里删除的产品记录取出来，放在删除表里。
    （2）从产品库存表里删除该产品记录。
    （3）从删除表里读出该产品的库存数量字段，判断是不是为零，如果为零的话，完成操作，从          内存里清除删除表；如果不为零的话，用Rollback Transaction语句来回滚操作。


2  Instead Of触发器的工作原理
    Instead Of触发器与After触发器不同。
    After触发器是在Insert、Update和Delete操作完成后才激活的，而Instead Of触发器，是在这些操作进行之前就激活了，并且不再去执行原来的SQL操作，而去运行触发器本身的SQL语句。


三、设计DML触发器的注意事项及技巧

  在了解触发器的种类和工作理由之后，现在可以开始动手来设计触发器了，不过在动手之前，还有一些注意事项必须先了解一下：

1.  设计触发器的限制

在触发器中，有一些SQL语句是不能使用的，这些语句包括：

  表1  在DML触发器中不能使用的语句

不能使用的语句            语句功能

Alter Database          修改数据库
Create Database        新建数据库
Drop Database          删除数据库
Load Database          导入数据库
Load Log                  导入日志
Reconfigure              更新配置 选项
Restore Database      还原数据库
Restore Log              还原数据库日志

另外，在对作为触发操作的目标的表或视图使用了下面的SQL语句时，不允许在DML触发器里再使用这些语句：

表2 在目标表中使用过的，DML触发器不能再使用的语句

不能使用的语句            语句功能

Create Index            建立索引
Alter Index                修改索引
Drop Index                删除索引
DBCC Dbreindex        重新生成索引
Alter Partition Function通过拆分或合并边界值更改分区
Drop Table                  删除数据表
Alter Table                  修改数据表结构

2.  如何在触发器取得字段修改前和修改后的数据

上面介绍过，SQL Server 2005在为每个触发器都定义了两个虚拟表，一个是插入表（inserted），一个是删除表（deleted），现在把这两个表存放的数据列表说明一下：

表3  插入/删除表的功能

激活触发器的动作    Inserted表            Deleted表

Insert                存放要插入的记录
Update              存放要更新的记录        存放更新前的旧记录
Delete              存放要删除的旧记录

1. ------删除触发器示例
2. --在删除时触发器要判断是否存在与此code一样的记录,如果存在则回滚,不允许删除.
3. --可以扩展为数量大于0,等条件.
4. create TRIGGER [dbo].[tr_after_del]
5.   ON  [dbo].[product]
6.   AFTER Delete
7. AS
8. BEGIN
9.     -- SET NOCOUNT ON added to prevent extra result sets from
10.     -- interfering with SELECT statements.
11. declare @code nvarchar(20)
12. select @code=code from deleted
13.     SET NOCOUNT ON;
14.     if exists(select * from stock where code=@code)
15.     begin
16. print '回滚操作'
17.     rollback tran
18.     insert stock
19.     select getdate(),code+convert(nvarchar,getdate(),112),qty,'回滚操作'
20.     from deleted
21.  
22. end
24. END

3.  其他注意事项

    After触发器只能用于数据表中，Instead Of触发器可以用于数据表和视图上，但两种触发器都不可以建立在临时表上。
    一个数据表可以有多个触发器，但是一个触发器只能对应一个表。
在同一个数据表中，对每个操作（如Insert、Update、Delete）而言可以建立许多个After触发器，但Instead Of触发器针对每个操作只有建立一个。
如果针对某个操作即设置了After触发器又设置了Instead Of触发器，那么Instead of触发器一定会激活，而After触发器就不一定会激活了。
    Truncate Table语句虽然类似于Delete语句可以删除记录，但是它不能激活Delete类型的触发器。因为Truncate Table语句是不记入日志的。
    WRITETEXT语句不能触发Insert和Update型的触发器。
    不同的SQL语句，可以触发同一个触发器，如Insert和Update语句都可以激活同一个触发器。


例二：

1. ----插入触发器
2. ---如果插入的code已经在库存表stock中存在了,则进行数量上的更新,否则就插入一条新的记录
3. ---可以扩展为在订单明细表里，折扣不能大于一个数值,如0.5，如果插入记录时，折扣大于0.5的话，回滚操作。
4. ---
5. create TRIGGER [dbo].[tr_after_ins]
6.   ON  [dbo].[product]
7.   AFTER INSERT
8. AS
9. BEGIN
10.     -- SET NOCOUNT ON added to prevent extra result sets from
11.     -- interfering with SELECT statements.
12. declare @code nvarchar(12)
13. declare @qty int
14. select @code=code,@qty=qty from inserted
15.     SET NOCOUNT ON;
16.     if not exists(select * from stock where code=@code)
17. begin
18.     insert [stock]
19.     select getdate(),@code,@qty,'添加操作'
20. print '添加操作'
21. end
22.     else
23.     update stock set qty=isnull(@qty,0)+qty ,dest=dest+'-->'+convert(nvarchar(5),qty)+'更新操作'
24.     where code=@code
26. END

完整示例:

1. CREATE TABLE [dbo].[stock](
2.     [dt] [datetime] NOT NULL,
3.     [code] [nvarchar](50) NULL,
4.     [qty] [int] NOT NULL DEFAULT ((6)),
6.     [dest] [nvarchar](250) null
7. ) ON [PRIMARY]
8. GO
9. CREATE TABLE [dbo].[product](
10. [name] [char](12) NOT NULL,
11.     [code] [nvarchar](50) not NULL,
12.     [qty] [int] NULL
13. ) ON [PRIMARY]
15. go

插入触发器

1. ----插入触发器
2. ---如果插入的code已经在库存表stock中存在了,则进行数量上的更新,否则就插入一条新的记录
3. ---可以扩展为在订单明细表里，折扣不能大于一个数值,如0.5，如果插入记录时，折扣大于0.5的话，回滚操作。
4. ---
5. create TRIGGER [dbo].[tr_after_ins]
6.   ON  [dbo].[product]
7.   AFTER INSERT
8. AS
9. BEGIN
10.     -- SET NOCOUNT ON added to prevent extra result sets from
11.     -- interfering with SELECT statements.
12. declare @code nvarchar(12)
13. declare @qty int
14. select @code=code,@qty=qty from inserted
15.     SET NOCOUNT ON;
16.     if not exists(select * from stock where code=@code)
17. begin
18.     insert [stock]
19.     select getdate(),@code,@qty,'添加操作'
20. print '添加操作'
21. end
22.     else
23.     update stock set qty=isnull(@qty,0)+qty ,dest=dest+'-->'+convert(nvarchar(5),qty)+'
25. 更新操作'
26.     where code=@code
27.     -- Insert statements for trigger here
29. END
30. go

更新触发器

1. --更新触发器
2. create TRIGGER [dbo].[tr_after_upd]
3.   ON  [dbo].[product]
4.   AFTER UPDATE
5. AS
6. BEGIN
7.     -- SET NOCOUNT ON added to prevent extra result sets from
8.     -- interfering with SELECT statements.
9. declare @code nvarchar(12)
10. declare @qty int
11. select @code=code,@qty=qty from inserted
12.     SET NOCOUNT ON;
13.     if exists(select * from stock where code=@code)
14. begin
15.     update stock set qty=isnull(@qty,0)+qty ,dest=dest+'-->'+'更新操作'
16.     where code=@code
17. print '更新操作'
18. end
19.     if @qty=999
20.     begin
21.     rollback tran
22.     print '更新回滚'
23.     end
24.     -- Insert statements for trigger here
26. END
28. go

删除触发器

1. ------删除触发器
2. --在删除时触发器要判断是否存在与此code一样的记录,如果存在则回滚,不允许删除.
3. --可以扩展为数量大于0,等条件.
4. create TRIGGER [dbo].[tr_after_del]
5.   ON  [dbo].[product]
6.   AFTER Delete
7. AS
8. BEGIN
9.     -- SET NOCOUNT ON added to prevent extra result sets from
10.     -- interfering with SELECT statements.
11. declare @code nvarchar(20)
12. select @code=code from deleted
13.     SET NOCOUNT ON;
14.     if exists(select * from stock where code=@code)
15.     begin
16. print '回滚操作'
17.     rollback tran
18.     insert stock
19.     select getdate(),code+convert(nvarchar,getdate(),112),qty,'回滚操作'
20.     from deleted
21.  
22. end
23.     -- Insert statements for trigger here
25. END
26. go

测试

1. create proc sp_test_tr_ins
2. @tr int=1
3. as
4. --测试如果回滚,触发器是否回滚
5. begin tran
6. insert [product]
7. select '插入','ins',1
8. if (@tr=1)
9. begin
10. commit tran
11. end
12. else
13. begin
14. rollback tran
15. end
16. go
17. create proc sp_test_tr_upd
18. @tr int=1,
19. @qty int=222
20. as
21. --测试如果回滚,触发器是否回滚
22. begin tran
23. update product set qty=@qty
24. where code='upd'
25. update product set qty=@qty
26. where code='roll'
27. if (@tr=1)
28. begin
29. commit tran
30. end
31. else
32. begin
33. rollback tran
34. end
35. go
36. create proc sp_test_tr_del
37. @tr int=1
38. as
39. --测试如果回滚,触发器是否回滚
40. begin tran
41. delete product
42. if (@tr=1)
43. begin
44. commit tran
45. end
46. else
47. begin
48. rollback tran
50. end
53. ---创建成功之后,应该如下图
54. go

附件: tr1.jpg

1. ------不使用事务
2. ---直接插入,更新,
3. insert [product]
4. select '插入','ins',11
5. insert [product]
6. select '更新','upd',111
7. insert [product]
9. select '回滚','roll',9

复制代码

--执行上面的代码之后,结果如下
附件: trins2.jpg

1. update product set qty=22
2. where code='ins'

-----执行上面的代码之后的结果如下,更新了库存
附件: trUpd1.jpg

1. update product set qty=999
3. where code='roll'

-----由于在触发器中做了设置,当数量为999时,则进行回滚.所以stock表中数量并没有进行更新.
附件: trUpd2.jpg

--由于在触发器使用了Rollback Transaction来回滚操作。
--所以使用下面的SQL语句来进行Delete操作的话，删除记录将会不成功。
delete product

----
附件: trdel1.jpg
go

----提交事务

sp_test_tr_ins
附件: trIns3.jpg
go

sp_test_tr_upd
附件: trUpd4.jpg
go
--更新回滚事务

sp_test_tr_upd 1,999
附件: trUpd5.jpg
go

sp_test_tr_del
附件: trdel2.jpg
go
---在存储过程中回滚事务

sp_test_tr_ins 2

go
sp_test_tr_upd 2

go
附件: trUpd7.jpg
sp_test_tr_del 2

附件: trDel3.jpg
go
select * from stock
select * from product

--删除测试数据

1. drop TRIGGER tr_after_ins
2. drop TRIGGER tr_after_del
3. drop TRIGGER tr_after_upd
4. drop proc sp_test_tr_ins
5. drop proc sp_test_tr_upd
7. drop proc sp_test_tr_del
8. drop table stock
9. drop table product