SQL Server 2005的DML触发器分After触发器的研讨

1.  企划触发器的界定

2.  什么在触发器取得字段修改前和修改后的多少

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

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

不可以运用的语句            语句功用

——删除触发器示例
–在剔除时触发器要看清是不是留存与此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

  
在摸底触发器的类型和做事理由之后,现在得以起来下手来设计触发器了,但是在下手之前,还有一些注意事项必须先驾驭一下:

—可以增添为在订单明细表里,折扣无法凌驾一个数值,如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中设有了,则举行数量上的换代,否则就插入一条新的记录

    在触发器中,有局地SQL语句是不可能动用的,那一个语句蕴含:

     1)当SQL Server接收到一个要举办删除操作的SQL语句时,SQL
Server先将要删除的笔录存放在剔除表里,

     2)把多少表里的记录删除,

表3  插入/删除表的效应

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

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

一、SQL Server 2005的DML触发器分为两类:
 1.
After触发器:那类触发器是在记录已经改变完之后(after),才会被激活执行,
        
它重如若用以记录变更后的处理或检查,一旦发现错误,也得以用Rollback
Transaction语句来回滚本次的操作。

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

地点介绍过,SQL Server
2005在为各类触发器都定义了四个虚拟表,一个是插入表(inserted),一个是删除表(deleted),现在把那三个表存放的数量列表说贝拉米(Bellamy)(贝拉米(Bellamy)(Bellamy))下:

其余,在对作为触发操作的靶子的表或视图使用了上面的SQL语句时,不允许在DML触发器里再利用那个语

无法应用的言辞             语句功效

  表1  在DML触发器中不可以运用的话语

表2 在对象表中使用过的,DML触发器不可以再使用的言语

       在SQL Server
2005里,为每个DML触发器都定义了七个奇特的表,一个是插入表(inserted),一个是删除表(deleted)。
      
那七个表是建在数据库服务器的内存中的,是由系统管理的逻辑表,而不是的确存储在数据库中的物理表。对于那七个表,用户只有读取的权位,没有改动的权限。
      
那四个表的布局与触发器所在数据表的结构是完全一致的,当触发器的办事到位之后,那四个表也将会从内存中删除。
      
插入表(inserted)里存放的是翻新前的记录:对于插入记录操作来说,插入表里存放的是要插入的数额;对于立异记录操作来说,插入表里存放的是要立异的笔录。
      
删除表(deleted)里存放的是翻新后的记录:对于创新记录操作来说,删除表里存放的是立异前的笔录(更新完后即被删除);对于删除记录操作来说,删除表里存入的是被剔除的旧记录。

 1.  After触发器的劳作规律
    
After触发器是在笔录更变落成之后(假设是在储存进度,而且有工作,则要在工作提交之后),
才会被激活并施行的。

3.  其余注意事项

 2  Instead Of触发器的做事原理
    Instead Of触发器与After触发器不一样。
    After触发器是在Insert、Update和Delete操作达成后才激活的,而Instead
Of触发器,是在那些操
作举行事先就激活了,并且不再去实施原来的SQL操作,而去运转触发器本身的SQL语句。

     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中设有了,则展开数量上的更新,否则就插入一条新的笔录

END

句:

 2. Instead
Of触发器:那类触发器一般是用来顶替原来的操作,在笔录变更从前暴发的,它并不去实施
本来SQL语句里的操作(Insert、Update、Delete),而去执行触发器本身所定义的操作。

—可以扩充为在订单明细表里,折扣无法压倒一个数值,如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图片 1
——不利用工作
—直接插入,更新,
insert [product]
select ‘插入’,’ins’,11
insert [product]
select ‘更新’,’upd’,111
insert [product]

select ‘回滚’,’roll’,9

–执行上边的代码之后,结果如下

图片 2

 

update product set qty=22
where code=’ins’
—–执行下边的代码之后的结果如下,更新了库存

图片 3

 

update product set qty=999

where code=’roll’

—–由于在触发器中做了安装,当数码为999时,则开展回滚.所以stock表中多少并不曾展开更新.

图片 4

–由于在触发器使用了Rollback Transaction来回滚操作。
–所以使用上面的SQL语句来开展Delete操作的话,删除记录将会不成功。
delete product


图片 5

go

—-提交事务

sp_test_tr_ins

图片 6 

go

sp_test_tr_upd

 

图片 7 

go
–更新回滚事务

sp_test_tr_upd 1,999

 

图片 8 

go

sp_test_tr_del

 

图片 9 

go
—在蕴藏进度中回滚事务

sp_test_tr_ins 2

go
sp_test_tr_upd 2

go

 

图片 10

sp_test_tr_del 2

 图片 11

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

     4) 触发器执行完结之后,删除内存中的删除表,退出整个操作。
    
仍旧举上边的事例:在成品库存表里,要是要刨除一条产品记录,在剔除记录时,触发器可以检查
该产品库存数量是不是为零,就算不为零则打消删除操作。看一下数据库是怎么操作的:
  
 (1)接收SQL语句,将要从成品库存表里删除的成品记录取出来,放在删除表里。
    (2)从产品库存表里删除该产品记录。
  
 (3)从删除表里读出该产品的库存数据字段,判断是或不是为零,倘使为零的话,已毕操作,从
           内存里清除删除表;若是不为零的话,用Rollback
Transaction语句来回滚操作。

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

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

相关文章