SQLServer 触发器

标签:
trigger触发器deleteinsertupdate |
分类: SQLServer |
SQL Server 触发器
by:授客 QQ:1033553122
什么是触发器
1.触发器是对表进行插入、更新、删除操作时自动执行的存储过程
2.触发器通常用于强制业务规则
3.触发器是一种高级约束,可以定义比check等约束更为复杂的约束
4.触发器定义在特定的表上,与表相关
5.自动触发执行,不能直接调用
6.是一个事务(可回滚,不能手动提交)
触发器的类型
delete触发器 当删除表中记录时触发,自动执行触发器所定义的SQL语句
insert触发器 当向表中插入数据时触发,自动执行触发器所定义的SQL语句
update触发器 当更新表中某列、多列时触发,自动执行触发器所定义的SQL语句
触发器触发时
inserted:用于存储 INSERT 和 UPDATE 语句所影响的行的副本。
1.在执行INSERT 或 UPDATE 语句时,新加行被同时添加到 inserted 表和触发器表中,所以inserted表临时保存了插入或更新后的记录行
2.可以从inserted中检查插入的数据是否满足业务需求,如果不满足,则向用户报告错误消息,并回滚插入操作
deleted表:表用于存储 DELETE 和 UPDATE 语句所影响的行的副本
1.在执行 DELETE 或 UPDATE 语句时,行从触发器表中删除,并传输到 deleted 表中,所以deleted表临时保存了删除或更新前的记录行
2.可从deleted表中检查被删除的数据是否满足业务需求, 如果不满足,则向用户报告错误消息,并回滚插入操作
注意:更新(UPDATE)语句类似于在删除之后执行插入;首先旧行被复制到 deleted 表中,然后新行被复制到触发器表和 inserted 表中
http://sucimg.itc.cn/sblog/of3b8e590487cc3da0c4f1aac54f338b0触发器" TITLE="SQLServer
如何创建触发器
1.创建触发器的语法
create trigger trigger_name
on table_name
for [delete|insert|update]
as
t-sql语句
go
http://sucimg.itc.cn/sblog/odfb9d9d1284e51989b15d3c4267e1252触发器" TITLE="SQLServer
1.
2.
3.
问题:
解决上述的银行取款问题:当向交易信息表(transInfo)中插入一条交易信息时,我们应自动更新对应帐户的余额。
分析:
1.在交易信息表上创建INSERT触发器2.从inserted临时表中获取插入的数据行
3.根据交易类型(transType)字段的值是存入/支取,
4.增加/减少对应帐户的余额。
create trigger trig_transInfo
on transInfo
for insert
as
declare @type char(4),@outMoney money
declare @myCardID char(10),@blance money
#从inserted表中获取交易类型、教员金额等
select @type=transType,@outMoney=transMoney,@myCardID=cardID from inserted
#根据交易类型,减少或增加对应卡号的余额
if(@type=’支取’)
update bank set currentMoney=currentMoney-@outMoney where cardID=@myCardID
else
update bank set currentMoney=currentMoney+@outMoney where cardID=@myCardID
……
go
http://sucimg.itc.cn/sblog/o033c1a908be51e58c003f6cd17f80ad6触发器" TITLE="SQLServer
delete触发器
问题
当删除交易信息表时,要求自动备份被删除的数据到表backupTable中
分析:
在交易信息表上创建delete触发器
被删除的数据可从deleted表中获取
注:delete删除触发器的典型应用就是银行系统中的数据备份。当交易记录过多时,为了不影响数据访问的速度,交易信息表需要定期删除部分数据。当删除数据时,一般需要自动备份,以便将来的客户查询、数据恢复或年终统计等。
create
trigger trig_delete_transInfo
on transInfo
for delete
as
print’开始备份数据,请稍后……’
if not exists(select * from sysobjects where name=’backupTable’)
select * into backupTable from deleted
else
insert into
backupTable select
print’备份数据成功,备份表中的数据为:’
select * from backupTable
go
http://sucimg.itc.cn/sblog/oc3b5681c0074fc50399ce1bbff689135触发器" TITLE="SQLServer
update触发器
update触发器的工作原理
http://sucimg.itc.cn/sblog/obbeaf349f1dc0686349ddcf2b52637af触发器" TITLE="SQLServer
说明:
执行更新操作,例如把李四的余额改为20001元。
更新操作可以看出两步:
1.删除李四原有的数据:李四
1000
0002
2.再插入新行:李四
1000
0002
最后看起来就是把余额从1元修改为20001元了。
所以:
如果我们希望查看修改前的原始数据,可以查看表deleted 。
如果我们希望查看修改后的数据,可以查看表inserted 。
问题:跟踪用户的交易,交易金额超过20000元,则取消交易,并给出错误提示。
分析:
在bank表上创建UPDATE触发器
修改前的数据可以从deleted表中获取;修改后的数据可以从inserted表中获取
注解:update更新触发器主要用于跟踪数据的变化。典型的应用就是银行系统中,为了安全起见,一般要求每次交易金额不能超过一定的数额。
create trigger trig_updadte_bank
on bank
for update
as
declare @beforeMoney money,@after Money money
select @beforeMoney=currentMoney from deleted
select @afterMoney=currentMoney from inserted
if abs(@afterMoney-@beforMoney)>20000
begin
print’交易金额:’+convert(varchar(8),abs(beforeMoney-afterMoney))
raiserror(‘每笔交易不能超过2万元’,交易失败’,16,1)
rollback transtraction
end
gohttp://sucimg.itc.cn/sblog/o8c57d8318b6e24b9df48a557e16ac533触发器" TITLE="SQLServer
UPDATE触发器除了跟踪数据的变化(修改)外,还可以检查是否修改了某列的数据
使用UPDATE(列)函数检测是否修改了某列
问题:
交易日期一般由系统自动产生,默认为当前日期。为了安全起见,一般禁止修改,以防舞弊
UPDATE(列名)函数可以检测是否修改了某列
注:UPDATE( )函数:测试在指定的列上进行的 INSERT 或 UPDATE 修改。
create trigger trig_update_transInfo
on transInfo
for update
as
if update(transDate) --检查是否修改了交易日期列transDate
begin
print’交易失败’
raiserror(‘安全警告:交易日期不能修改,由系统自动产生’,16,1)
rollback transtraction
end
go
http://sucimg.itc.cn/sblog/o16d59b2e44f3aa9dca6ba62ca2654a8a触发器" TITLE="SQLServer
总结:
触发器是在对表进行插入、更新或删除操作时自动执行的存储过程,触发器通常用于强制业务规则
触发器还是一个特殊的事务单元,当出现错误时,可以执行ROLLBACK TRANSACTION回滚撤销操作
触发器一般都需要使用临时表:deleted表和inserted表,它们存放了被删除或插入的记录行副本
触发器类型:INSERT触发器、UPDATE触发器、DELETE触发器