SQL ServerSQL 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 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               存放要去除的原记录

——删除触发器示例
–在剔除时触发器要判是否存在和这个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]

    select getdate(),@code,@qty,’添加操作’
    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

    begin
    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

end

 

—创建成功后,应该如下图

go
——不使工作
—直接插入,更新,
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

 

go

sp_test_tr_upd

 

 

go
–更新回滚事务

sp_test_tr_upd 1,999

 

 

go

sp_test_tr_del

 

 

go
—在蕴藏过程被回滚事务

sp_test_tr_ins 2

go
sp_test_tr_upd 2

go

 

sp_test_tr_del 2

 

go
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

相关文章