SQL Server 触发器
by:授客 QQ：1033553122
什么是触发器
1.触发器是对表进行插入、更新、删除操作时自动执行的存储过程
2.触发器通常用于强制业务规则
3.触发器是一种高级约束，可以定义比check等约束更为复杂的约束
可执行复杂的sql语句(if/while/case)
可引用其它表中的列
4.触发器定义在特定的表上，与表相关
5.自动触发执行，不能直接调用
6.是一个事务(可回滚，不能手动提交)
触发器的类型
delete触发器 当删除表中记录时触发，自动执行触发器所定义的SQL语句
insert触发器 当向表中插入数据时触发，自动执行触发器所定义的SQL语句
update触发器 当更新表中某列、多列时触发，自动执行触发器所定义的SQL语句
deleted和inserted表
触发器触发时
1.系统自动在内存中创建deleted表或inserted表
2.只读，不允许修改；触发器执行完后，自动删除
inserted：用于存储 INSERT 和 UPDATE 语句所影响的行的副本。
1.在执行INSERT 或 UPDATE 语句时，新加行被同时添加到 inserted 表和触发器表中，所以inserted表临时保存了插入或更新后的记录行
2.可以从inserted中检查插入的数据是否满足业务需求，如果不满足，则向用户报告错误消息，并回滚插入操作
deleted表：表用于存储 DELETE 和 UPDATE 语句所影响的行的副本
1.在执行 DELETE 或 UPDATE 语句时，行从触发器表中删除，并传输到 deleted 表中，所以deleted表临时保存了删除或更新前的记录行
2.可从deleted表中检查被删除的数据是否满足业务需求， 如果不满足，则向用户报告错误消息，并回滚插入操作
注意：更新（UPDATE）语句类似于在删除之后执行插入；首先旧行被复制到 deleted 表中，然后新行被复制到触发器表和 inserted 表中
如何创建触发器
1.创建触发器的语法
create trigger trigger_name
on table_name
for [delete|insert|update]
as
t-sql语句
go
2.insert触发器的工作原理
说明：
1. 执行insert插入语句，在表中插入数据行
2. 触发insert触发器，向系统临时表inserted表中插入新行的副本
3. 触发器检查inserted表中插入的新行数据，确定是搜需要回滚或执行其他操作。
问题：
解决上述的银行取款问题：当向交易信息表(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
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 * from deleted
print’备份数据成功，备份表中的数据为：’
select * from backupTable
go
update触发器
update触发器的工作原理
说明：
执行更新操作，例如把李四的余额改为20001元。
更新操作可以看出两步：
1.删除李四原有的数据：李四 1000 0002 1，将数据备份到deleted表中。
2.再插入新行：李四 1000 0002 20001，将数据备份到inserted表中。
最后看起来就是把余额从1元修改为20001元了。
所以：
如果我们希望查看修改前的原始数据，可以查看表deleted 。
如果我们希望查看修改后的数据，可以查看表inserted 。
问题：跟踪用户的交易，交易金额超过20000元，则取消交易，并给出错误提示。
分析：
在bank表上创建UPDATE触发器
修改前的数据可以从deleted表中获取;修改后的数据可以从inserted表中获取
注解：update更新触发器主要用于跟踪数据的变化。典型的应用就是银行系统中，为了安全起见，一般要求每次交易金额不能超过一定的数额。
用 户每次的交易金额，我们可以从交易信息表中直接获取，也可以根据帐号信息表中余额的变化来获取。交易的方式较多，用户可能用卡消费，也可能用存折消费，存 折的交易信息与卡略有不同，可能不会将交易信息存放在交易表中，而保存在其它表中。但存折和卡对应的帐号余额只有一个。所以更安全的方案就是：根据账户信 息表中余额的变化来获取交易金额。为了获取交易余额的变化，我们应该在账户的信息表上创建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
go
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
总结：
触发器是在对表进行插入、更新或删除操作时自动执行的存储过程，触发器通常用于强制业务规则
触发器还是一个特殊的事务单元，当出现错误时，可以执行ROLLBACK TRANSACTION回滚撤销操作
触发器一般都需要使用临时表：deleted表和inserted表，它们存放了被删除或插入的记录行副本
触发器类型：INSERT触发器、UPDATE触发器、DELETE触发器
