SQL server语句大全(7)

create trigger trig_del_transinfo on transinfo ----表

for delete ----删除触发器 as

----------判断backup table是否存在,不存在的就创建

if not exists(select * from sysobjects where name='backupTable') ----------创建表,并插入删除的数据 -----select into select * into backupTable from deleted else -----backupTable存在

insert into backupTable select * from deleted print '成功备份数据,备份数据为:' select * from backupTable go

select * from transinfo

delete from transinfo where cardid='1002'

----------------------------------------------------------------delete触发器

----------针对账户表禁止进行删除操作,用触发器实现此功能 create trigger trig_notdelete on banks for delete as

print '禁止删除数据'

rollback --------事物回滚,因为触发器是一个事物 go

-----------测试

delete from banks where cardid ='1001' select * from banks

-------------------------------------------------------------------------------update触发器

-----------跟踪用户的交易,交易金额超过20000元,则取消交易,并给出错误提示 --------分析:banks表上创建update触发器

-------------修改前的数据可以从deleted表中获取 -------------修改后的数据从inserted 表中获取 create trigger trig_Error on banks for update as

declare @beformoney money declare @aftermoney money

select @beformoney=currentmoney from deleted -----修改前的数据可以从deleted表中获取

select @aftermoney =currentmoney from inserted ---------修改后的数据从inserted 表中获取

if(ABS(@aftermoney-@beformoney) >20000) ----ABS()取绝对值 begin

print '交易金额'+cast(ABS(@aftermoney-@beformoney) as varchar) raiserror('每笔交易不能超过20000元',1,1)

----raiserror('显示错误信息',错误级别(0-18),错误状态)--------错误描述\\提示 rollback ----回滚,取消交易 end go

---------------------------删除触发器 drop trigger trig_Error

---------------------------------------------------触发器测试 select * from banks

update banks set currentmoney =currentmoney+40000 from banks where cardid ='1002'

update banks set currentmoney =currentmoney+200 from banks where cardid ='1002'