sqlserver触发器使用举例
发布时间：2020-12-12 14:57:33 所属栏目：MsSql教程 来源：网络整理
导读：--------------INSERT触发器--------------------------? alter trigger trig_transinfo_insert? on transinfo? for insert,update? as? print '开始执行触发器------------------------------'? print '临时表的内容：：----------'? select * from inserte
|
--------------INSERT触发器--------------------------? alter trigger trig_transinfo_insert? on transinfo? for insert,update? as? print '开始执行触发器------------------------------'? print '临时表的内容：：----------'? select * from inserted? print '-------------------'? declare @type varchar(20)? declare @transmoney money? declare @cardid varchar(20)? set @type=(select transtype from inserted )? set @transmoney=(select transmoney from inserted )? set @cardid=(select cardid from inserted )? print @type +'?? '+@cardid? if(@type='存入')? begin? update bank set currentmoney=? currentmoney+@transmoney where cardid=@cardid? select * from bank? end? else? begin? update bank set currentmoney=? currentmoney-@transmoney where cardid=@cardid? select * from bank? end? update transinfo set transmoney=500? where cardid='8505 001' and transtype='支取'? print '触发器执行完毕'? go? update bank set cardid='8505 001' where usernam='张荧'? update bank set cardid='8505 002' where usernam='王博'? select * from transinfo? insert into transinfo values? ('8505 001','支取',200,default)? insert into transinfo values? ('8505 002','存入',default)? ----========删除触发器=========? alter trigger trig_transinfo_delete? on transinfo? for delete? as? print '开始执行删除触发器'? print '? '? select * from deleted? --==========把删除数据备份到新表中? if exists(select * from sysobjects where name='newTable')? --如果备份表存在，就添加新数据到备份表? insert into newtable select *? from deleted where datediff(mm,transdate,getdate())>=1? else --如果备份表不存在，就用deleted创建一个新的备份表? select * into newTable from deleted where datediff(mm,getdate())>=1? select * from newtable? select * from transinfo? print '备份结束'? go? select * from transinfo? delete from transinfo where cardid='8505 001' and? transtype='存入'? （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |