【T-SQL】九、DML触发器

九、DML触发器

（〇）语法格式：

CREATE TRIGGER 触发器
ON <表名|视图名>
FOR |AFTER|INSTEAD OF
[INSERT][，][DELETE][，][UPDATE]
AS 
[BEGIN]
T-SQL语句
[END]

将DML触发器分为INSERT、DELETE、UPDATE这3类。

• INSERT触发器：当数据库表的INSERT触发器执行时，同时将插入新记录到该数据库表和inserted表。
• DELETE触发器：当数据库表的DELETE触发器执行时，从数据库表删除的数据首先放到deleted表。
• UPDATE触发器：当数据库的UPDATE触发器执行时系统首先删除原有的记录，并将原有的记录插入到deleted表中，插入的新记录也同时插入到inserted表中。

（一）创建INSERT事件的AFTER触发器

INSERT事件的AFTER触发器是对于指定的表，在执行数据行插入语句INSERT…INTO…VALUES事件之后被激发的一段程序代码。

-- 定义一个INSERT触发器，在数据库“Library”的表“borrow”中添加一行借阅信息时，借阅该书的读者表“reader”中的借阅数量“lendum”增加1本。
-- 创建insert事件after触发器
USE Library
if exists(select name from sysobjects        --如果已有触发器tr_borrow_date
where name='tr_borrow_date' and type='tr')  
drop trigger tr_borrow_date                  --删除触发器tr_borrow_date
GO
create trigger tr_borrow_date                --创建触发器tr_borrow_date
on borrow                                    --基于表"borrow"
after insert                                 --insert事件之后触发
as
begin
 declare @dzbh char(10)
 --inserted表中查询出读者编号rid赋值给局部变量@dzbh
 set @dzbh=(select rid from inserted)     
update reader                             --读者表中该读者借阅数量加1
 set lendnum=lendnum+1
 where rid=@dzbh
end 
GO
-- 执行以上代码后，即可在数据库“Library”表“borrow”中创建存储过程“tr_borrow_date”。
-- 若执行插入数据行T-SQL语句
USE Library
insert into borrow(rid,bid)values('2001050001','TP311-051')

（二）创建DELETE事件的AFTER触发器

DELETE事件的AFTER触发器是对于指定的表，在执行数据行DELETE语句事件之后被激发的一段程序代码。

-- 定义一个DELETE触发器，在数据库“Library”的表“reader”中删除一行借读者信息时，检查该读者是否有书未还，若有书未还不能删除该读者。
-- 创建delete事件的after触发器
USE Library
if exists(select name from sysobjects
where name='reader_delafter' and type='tr')
drop trigger reader_delafter
GO
create trigger reader_delafter 
on reader
for delete                        --创建基于delete事件的after触发器
as
begin
 declare @data_jy int
  select @data_jy=lendnum          --从临时表中获取借阅数量
  from deleted
  if @data_jy>0                    --借阅数量大于说明有未还的图书
   begin
    print'该读者不能删除！还有'+convert(char(2),@data_jy)+'本书没还'
    rollback                       --事务回滚取消所删除的数据行
   end
  else
   print '该读者已被删除！！！'    --显示行已经删除
end
GO
-- 执行以上代码后，即可在数据库“Library”表“reader”中创建存储过程“reader_delafter”。
-- 若执行删除数据行T-SQL语句：
USE Library
GO
delete reader where rid='2009051001'
-- 执行以上代码后，会出现如下提示信息：
-- 该读者不能删除！还有2本书没还
-- 事务在触发器中结束。批处理已中止。

（三）创建UPDATE事件的AFTER触发器

UPDATE事件的AFTER触发器是对于指定的表，在执行数据行UPDATE语句事件之后被激发的一段程序代码。

-- 定义一个UPDATE触发器，保护数据库“Library”的表“ReaderType”中“限借数量”和“限借天数”不能任意修改。
USE Library
GO
create trigger rt_update
on ReaderType
for update
as
 if (update(LimitNum)or update(LimitDays))        --update函数保护两属性列
  begin
     print '事务不能被处理，基础数据不能修改！'
     rollback
   end
 else
   print '数据修改成功！' 
-- 执行以上代码后，即可在数据库“Library”表“ReaderType”中创建存储过程“rt_update”。
-- 若执行更新数据行T-SQL语句：
   update  ReaderType
   set LimitNum=10
   where TypeID=2
-- 执行以上代码后，会出现如下提示信息：
-- 事务不能被处理，基础数据不能修改！
-- 事务在触发器中结束。批处理已中止。

（四）创建DELETE事件的INSTEAD OF触发器

DELETE事件的INSTEAD OF触发器是对于指定的表或视图，在执行DELETE语句事件发生时用来替代DELETED事件的一段程序代码。

-- 定义一个INSTEAD OF触发器，在数据库“Library”的表“reader”中删除一行借读者信息前，检查该读者是否有书未还，若有书未还不能删除该读者。
-- 创建delete事件的instead of触发器
USE Library
if exists(select name from sysobjects
where name='reader_delinstead' and type='tr')
drop trigger reader_delinstead
GO
create trigger reader_delinstead 
on reader
INSTEAD OF delete                 --创建基于delete事件的INSTEAD OF 触发器
as
begin
  declare @data_jy int
  select @data_jy=lendnum          --从临时表中获取借阅数量
  from deleted
  if @data_jy>0                    --借阅数量大于说明有未还的图书
   begin
    print '该读者不能删除！还有'+convert(char(2),@data_jy)+'本书没还'
    rollback                       --事务回滚取消所删除的数据行
   end
else
      begin
      delete reader where rid= (select RID from deleted)
      print '该读者已被删除！！！'    --显示行已经删除
      end
end
GO
-- 执行以上代码后，即可在数据库“Library”表“reader”中创建存储过程“reader_ delinstead”。
-- 若执行删除数据行T-SQL语句：USE Library
GO
delete reader where rid='2009051001'
-- 执行以上代码后，会出现如下提示信息：
-- 该读者不能删除！还有2本书没还
-- 事务在触发器中结束。批处理已中止。