触发器练习

create database Bank
go

create table bankInfo
(
	customerName varchar(10) ,
	cardid varchar(10),
	currMoney money
	
)
create table transInfo
(
	cardid varchar(10),
	transMoney money,
	transType varchar(10),
	transDate datetime
)

insert into bankInfo values ('张三','1001',1000)
insert into bankInfo values ('张四','1000',1)

alter table bankInfo add constraint CK_currMoney check(currMoney>=1)

select * from transInfo

/*
当向交易信息表(transInfo)中插入一条交易信息时，
我们应自动更新对应帐户的余额
*/
if exists(select * from sysobjects where name='trig_transInfo_insert')
	drop trigger trig_transInfo_insert
go
create trigger trig_transInfo_insert
on transInfo 
for insert 
as
	declare @cardid varchar(10)
	declare @transMoney money
	declare @transType varchar(10)
	select @cardid=cardid,@transMoney=transMoney,@transType=transType from inserted
	if(@transType='存入')
	begin
		update bankInfo set currMoney=currMoney+@transMoney where cardid=@cardid
	end
	else
	begin
		update bankInfo set currMoney=currMoney-@transMoney where cardid=@cardid
	end
go
	
insert into transInfo values ('1001',500,'存入','2014-02-15')
insert into transInfo values ('1000',100,'存入','2014-02-18')

select * from bankInfo
select * from transInfo

/*
当删除交易信息表时，要求自动备份被删除的数据到表backupTable中 
*/
if exists (select * from sysobjects where name='trig_transInfo_del')
	drop trigger trig_transInfo_del
go
create trigger trig_transInfo_del
on transInfo 
for delete
as
	if not exists (select * from sysobjects where name='backupTable')
		select  * into backupTable from deleted
	else
		insert into backupTable 
		select * from deleted	
go

delete from transInfo where cardid='1000'

select * from backuptable
go

create trigger trig_bankInfo_update
on bankInfo
for update
as
	declare @beforeMoney money
	declare @afterMoney money
	select @beforeMoney=currMoney from deleted
	select @afterMoney=currMoney from inserted
	if (abs(@afterMoney-@beforeMoney)>20000)
	begin
		raiserror('交易额度不能大于2W',16,1)
		rollback
	end
	else
		print '交易成功'
go

select * from bankInfo
update bankInfo set currMoney=5000 where cardid='1001'

select * from bankInfo
select * from transInfo
	

if exists (select * from sysobjects where name='trig_transInfo_updateDate')
	drop trigger trig_transInfo_updateDate
go
create trigger trig_transInfo_updateDate
on transInfo
for update
as
	if UPDATE(transDate)
	begin
		print '修改失败'
		raiserror('交易日期不能修改，不能作弊，上课不能打瞌睡',18,1)
		rollback 
	end
go

update transInfo set transDate='2014-02-14' where cardid='1001'