SQL Server 2005的DML触发器分After触发器的研究
一、SQL Server 2005的DML触发器分为两类:
1. After触发器:这类触发器是在记录已经改变完之后(after),才会被激活执行,
它主要是用于记录变更后的处理或检查,一旦发现错误,也可以用Rollback Transaction语句来回滚本次的操作。
2. Instead Of触发器:这类触发器一般是用来取代原本的操作,在记录变更之前发生的,它并不去执行
原来SQL语句里的操作(Insert、Update、Delete),而去执行触发器本身所定义的操作。
在SQL Server 2005里,为每个DML触发器都定义了两个特殊的表,一个是插入表(inserted),一个是删除表(deleted)。
这两个表是建在数据库服务器的内存中的,是由系统管理的逻辑表,而不是真正存储在数据库中的物理表。对于这两个表,用户只有读取的权限,没有修改的权限。
这两个表的结构与触发器所在数据表的结构是完全一致的,当触发器的工作完成之后,这两个表也将会从内存中删除。
插入表(inserted)里存放的是更新前的记录:对于插入记录操作来说,插入表里存放的是要插入的数据;对于更新记录操作来说,插入表里存放的是要更新的记录。
删除表(deleted)里存放的是更新后的记录:对于更新记录操作来说,删除表里存放的是更新前的记录(更新完后即被删除);对于删除记录操作来说,删除表里存入的是被删除的旧记录。
二、触发器的工作原理
1. After触发器的工作原理
After触发器是在记录更变完成之后(如果是在存储过程,而且有事务,则要在事务提交之后), 才会被激活并执行的。
还是举上面的例子:在产品库存表里,如果要删除一条产品记录,在删除记录时,触发器可以检查
该产品库存数量是否为零,如果不为零则取消删除操作。看一下数据库是怎么操作的:
(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 存放要删除的旧记录
------删除触发器示例
--在删除时触发器要判断是否存在与此code一样的记录,如果存在则回滚,不允许删除.
--可以扩展为数量大于0,等条件.
create TRIGGER [dbo].[tr_after_del]
ON [dbo].[product]
AFTER Delete
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
declare @code nvarchar(20)
select @code=code from deleted
SET NOCOUNT ON;
if exists(select * from stock where code=@code)
begin
print '回滚操作'
rollback tran
insert stock
select getdate(),code+convert(nvarchar,getdate(),112),qty,'回滚操作'
from deleted
end
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语句都可以激活同一个触发器。
例二:
----插入触发器
---如果插入的code已经在库存表stock中存在了,则进行数量上的更新,否则就插入一条新的记录
---可以扩展为在订单明细表里,折扣不能大于一个数值,如0.5,如果插入记录时,折扣大于0.5的话,回滚操作。
---
create TRIGGER [dbo].[tr_after_ins]
ON [dbo].[product]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
declare @code nvarchar(12)
declare @qty int
select @code=code,@qty=qty from inserted
SET NOCOUNT ON;
if not exists(select * from stock where code=@code)
begin
insert [stock]
select getdate(),@code,@qty,'添加操作'
print '添加操作'
end
else
update stock set qty=isnull(@qty,0)+qty ,dest=dest+'-->'+convert(nvarchar(5),qty)+'
更新操作'
where code=@code
END
完整示例:
CREATE TABLE [dbo].[stock](
[dt] [datetime] NOT NULL,
[code] [nvarchar](50) NULL,
[qty] [int] NOT NULL DEFAULT ((6)),
[dest] [nvarchar](250) null
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[product](
[name] [char](12) NOT NULL,
[code] [nvarchar](50) not NULL,
[qty] [int] NULL
) ON [PRIMARY]
go
----插入触发器
---如果插入的code已经在库存表stock中存在了,则进行数量上的更新,否则就插入一条新的记录
---可以扩展为在订单明细表里,折扣不能大于一个数值,如0.5,如果插入记录时,折扣大于0.5的话,回滚操作。
---
create TRIGGER [dbo].[tr_after_ins]
ON [dbo].[product]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
declare @code nvarchar(12)
declare @qty int
select @code=code,@qty=qty from inserted
SET NOCOUNT ON;
if not exists(select * from stock where code=@code)
print '添加操作'
end
else
update stock set qty=isnull(@qty,0)+qty ,dest=dest+'-->'+convert(nvarchar(5),qty)+'更新操作'
where code=@code
-- Insert statements for trigger here
END
go
--更新触发器
create TRIGGER [dbo].[tr_after_upd]
ON [dbo].[product]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
declare @code nvarchar(12)
declare @qty int
select @code=code,@qty=qty from inserted
SET NOCOUNT ON;
if exists(select * from stock where code=@code)
begin
update stock set qty=isnull(@qty,0)+qty ,dest=dest+'-->'+'更新操作'
where code=@code
print '更新操作'
end
rollback tran
print '更新回滚'
end
-- Insert statements for trigger here
END
go
------删除触发器
--在删除时触发器要判断是否存在与此code一样的记录,如果存在则回滚,不允许删除.
--可以扩展为数量大于0,等条件.
create TRIGGER [dbo].[tr_after_del]
ON [dbo].[product]
AFTER Delete
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
declare @code nvarchar(20)
select @code=code from deleted
SET NOCOUNT ON;
if exists(select * from stock where code=@code)
begin
print '回滚操作'
rollback tran
insert stock
select getdate(),code+convert(nvarchar,getdate(),112),qty,'回滚操作'
from deleted
end
-- Insert statements for trigger here
END
go
create proc sp_test_tr_ins
@tr int=1
as
--测试如果回滚,触发器是否回滚
begin tran
insert [product]
select '插入','ins',1
if (@tr=1)
begin
commit tran
end
else
begin
rollback tran
end
go
create proc sp_test_tr_upd
@tr int=1,
@qty int=222
as
--测试如果回滚,触发器是否回滚
begin tran
update product set qty=@qty
where code='upd'
update product set qty=@qty
where code='roll'
if (@tr=1)
begin
commit tran
end
else
begin
rollback tran
end
go
create proc sp_test_tr_del
@tr int=1
as
--测试如果回滚,触发器是否回滚
begin tran
delete product
if (@tr=1)
begin
commit tran
end
else
begin
rollback tran
![](https://www.cnblogs.com/images/cnblogs_com/chillsrc/tr1.jpg)
------不使用事务
---直接插入,更新,
insert [product]
select '插入','ins',11
insert [product]
select '更新','upd',111
insert [product]
where code='ins'
-----执行上面的代码之后的结果如下,更新了库存
--由于在触发器使用了Rollback Transaction来回滚操作。
--所以使用下面的SQL语句来进行Delete操作的话,删除记录将会不成功。
delete product
--更新回滚事务
---在存储过程中回滚事务
sp_test_tr_upd 2
select * from stock
select * from product
--删除测试数据
drop TRIGGER tr_after_ins
drop TRIGGER tr_after_del
drop TRIGGER tr_after_upd
drop proc sp_test_tr_ins
drop proc sp_test_tr_upd
drop proc sp_test_tr_del
drop table stock
drop table product
1. After触发器:这类触发器是在记录已经改变完之后(after),才会被激活执行,
它主要是用于记录变更后的处理或检查,一旦发现错误,也可以用Rollback Transaction语句来回滚本次的操作。
2. Instead Of触发器:这类触发器一般是用来取代原本的操作,在记录变更之前发生的,它并不去执行
原来SQL语句里的操作(Insert、Update、Delete),而去执行触发器本身所定义的操作。
在SQL Server 2005里,为每个DML触发器都定义了两个特殊的表,一个是插入表(inserted),一个是删除表(deleted)。
这两个表是建在数据库服务器的内存中的,是由系统管理的逻辑表,而不是真正存储在数据库中的物理表。对于这两个表,用户只有读取的权限,没有修改的权限。
这两个表的结构与触发器所在数据表的结构是完全一致的,当触发器的工作完成之后,这两个表也将会从内存中删除。
插入表(inserted)里存放的是更新前的记录:对于插入记录操作来说,插入表里存放的是要插入的数据;对于更新记录操作来说,插入表里存放的是要更新的记录。
删除表(deleted)里存放的是更新后的记录:对于更新记录操作来说,删除表里存放的是更新前的记录(更新完后即被删除);对于删除记录操作来说,删除表里存入的是被删除的旧记录。
二、触发器的工作原理
1. After触发器的工作原理
After触发器是在记录更变完成之后(如果是在存储过程,而且有事务,则要在事务提交之后), 才会被激活并执行的。
以删除记录为例,分为以下步骤:
1)当SQL Server接收到一个要执行删除操作的SQL语句时,SQL Server先将要删除的记录存放在删除表里,
2)把数据表里的记录删除,
3)激活After触发器,执行After触发器里的SQL语句。
还是举上面的例子:在产品库存表里,如果要删除一条产品记录,在删除记录时,触发器可以检查
该产品库存数量是否为零,如果不为零则取消删除操作。看一下数据库是怎么操作的:
(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 存放要删除的旧记录
------删除触发器示例
--在删除时触发器要判断是否存在与此code一样的记录,如果存在则回滚,不允许删除.
--可以扩展为数量大于0,等条件.
create TRIGGER [dbo].[tr_after_del]
ON [dbo].[product]
AFTER Delete
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
declare @code nvarchar(20)
select @code=code from deleted
SET NOCOUNT ON;
if exists(select * from stock where code=@code)
begin
print '回滚操作'
rollback tran
insert stock
select getdate(),code+convert(nvarchar,getdate(),112),qty,'回滚操作'
from deleted
end
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语句都可以激活同一个触发器。
例二:
----插入触发器
---如果插入的code已经在库存表stock中存在了,则进行数量上的更新,否则就插入一条新的记录
---可以扩展为在订单明细表里,折扣不能大于一个数值,如0.5,如果插入记录时,折扣大于0.5的话,回滚操作。
---
create TRIGGER [dbo].[tr_after_ins]
ON [dbo].[product]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
declare @code nvarchar(12)
declare @qty int
select @code=code,@qty=qty from inserted
SET NOCOUNT ON;
if not exists(select * from stock where code=@code)
begin
insert [stock]
select getdate(),@code,@qty,'添加操作'
print '添加操作'
end
else
update stock set qty=isnull(@qty,0)+qty ,dest=dest+'-->'+convert(nvarchar(5),qty)+'
更新操作'
where code=@code
END
完整示例:
CREATE TABLE [dbo].[stock](
[dt] [datetime] NOT NULL,
[code] [nvarchar](50) NULL,
[qty] [int] NOT NULL DEFAULT ((6)),
[dest] [nvarchar](250) null
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[product](
[name] [char](12) NOT NULL,
[code] [nvarchar](50) not NULL,
[qty] [int] NULL
) ON [PRIMARY]
go
----插入触发器
---如果插入的code已经在库存表stock中存在了,则进行数量上的更新,否则就插入一条新的记录
---可以扩展为在订单明细表里,折扣不能大于一个数值,如0.5,如果插入记录时,折扣大于0.5的话,回滚操作。
---
create TRIGGER [dbo].[tr_after_ins]
ON [dbo].[product]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
declare @code nvarchar(12)
declare @qty int
select @code=code,@qty=qty from inserted
SET NOCOUNT ON;
if not exists(select * from stock where code=@code)
begin
insert [stock]
print '添加操作'
end
else
update stock set qty=isnull(@qty,0)+qty ,dest=dest+'-->'+convert(nvarchar(5),qty)+'更新操作'
where code=@code
-- Insert statements for trigger here
END
go
--更新触发器
create TRIGGER [dbo].[tr_after_upd]
ON [dbo].[product]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
declare @code nvarchar(12)
declare @qty int
select @code=code,@qty=qty from inserted
SET NOCOUNT ON;
if exists(select * from stock where code=@code)
begin
update stock set qty=isnull(@qty,0)+qty ,dest=dest+'-->'+'更新操作'
where code=@code
print '更新操作'
end
---如果数量为999则进行回滚操作
if @qty=999
beginrollback tran
print '更新回滚'
end
-- Insert statements for trigger here
END
go
------删除触发器
--在删除时触发器要判断是否存在与此code一样的记录,如果存在则回滚,不允许删除.
--可以扩展为数量大于0,等条件.
create TRIGGER [dbo].[tr_after_del]
ON [dbo].[product]
AFTER Delete
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
declare @code nvarchar(20)
select @code=code from deleted
SET NOCOUNT ON;
if exists(select * from stock where code=@code)
begin
print '回滚操作'
rollback tran
insert stock
select getdate(),code+convert(nvarchar,getdate(),112),qty,'回滚操作'
from deleted
end
-- Insert statements for trigger here
END
go
create proc sp_test_tr_ins
@tr int=1
as
--测试如果回滚,触发器是否回滚
begin tran
insert [product]
select '插入','ins',1
if (@tr=1)
begin
commit tran
end
else
begin
rollback tran
end
go
create proc sp_test_tr_upd
@tr int=1,
@qty int=222
as
--测试如果回滚,触发器是否回滚
begin tran
update product set qty=@qty
where code='upd'
update product set qty=@qty
where code='roll'
if (@tr=1)
begin
commit tran
end
else
begin
rollback tran
end
go
create proc sp_test_tr_del
@tr int=1
as
--测试如果回滚,触发器是否回滚
begin tran
delete product
if (@tr=1)
begin
commit tran
end
else
begin
rollback tran
end
---创建成功之后,应该如下图
![](https://www.cnblogs.com/images/cnblogs_com/chillsrc/tr1.jpg)
------不使用事务
---直接插入,更新,
insert [product]
select '插入','ins',11
insert [product]
select '更新','upd',111
insert [product]
select '回滚','roll',9
--执行上面的代码之后,结果如下
update product set qty=22
where code='ins'
-----执行上面的代码之后的结果如下,更新了库存
update product set qty=999
where code='roll'
-----由于在触发器中做了设置,当数量为999时,则进行回滚.所以stock表中数量并没有进行更新.
--由于在触发器使用了Rollback Transaction来回滚操作。
--所以使用下面的SQL语句来进行Delete操作的话,删除记录将会不成功。
delete product
----
go
----提交事务sp_test_tr_ins
sp_test_tr_upd
--更新回滚事务
sp_test_tr_upd 1,999
sp_test_tr_del
---在存储过程中回滚事务
sp_test_tr_ins 2
sp_test_tr_upd 2
go
sp_test_tr_del 2
select * from stock
select * from product
--删除测试数据
drop TRIGGER tr_after_ins
drop TRIGGER tr_after_del
drop TRIGGER tr_after_upd
drop proc sp_test_tr_ins
drop proc sp_test_tr_upd
drop proc sp_test_tr_del
drop table stock
drop table product