sql trigger

08月14日(星期二)

 

create database mydb
go
use mydb
go
drop table stuinfo
drop table stumarks

if exists(select * from sysobjects where name ='bank')
drop table bank
go
create table bank
(
cardid varchar(20) not null primary key,
customname varchar(10) not null ,
currentmoney money not null check(currentmoney>=1)

)
if exists (select * from sysobjects where name='transinfo')
drop table transinfo
go
create table transinfo
(
cardid varchar(20) not null foreign key references bank(cardid),
chargetype varchar(4) not null ,
tmoney money
)
insert into bank values('10001000','张三',120000)
insert into bank values('10001001','李四',1)
create proc look
as
select * from bank
select * from transinfo
go

set nocount on

exec look

 

--insert触发器
create trigger transinfo_trig_inserte on transinfo with encryption
for insert
as
begin transaction

declare @money money,
@cardid varchar(20),
@type varchar(4),
@bankmoney money
select @type=chargetype,@money=tmoney,@cardid=cardid from inserted
select @bankmoney=currentmoney from bank where cardid=@cardid
if(@type='支取')
begin
if(@bankmoney-@money>1)
begin
update bank set currentmoney=currentmoney - @money where cardid=@cardid
commit transaction
end
else
begin
print'操作失败，余额不足！'
rollback transaction
end
end
else
begin
update bank set currentmoney=currentmoney + @money where cardid=@cardid
commit transaction
end
exec look
go

insert into transinfo values('10001001','存入',1000)
insert into transinfo values('10001001','支取',20000)
insert into transinfo values('10001001','支取',200)
insert into transinfo values('10001000','支取',1000)
--delete触发器
alter trigger transinfo_trig_del on transinfo with encryption
for delete
as

declare @cardid varchar(20),
@money money,
@cmoney money,
@type varchar(4)
select @cardid =cardid,@money=tmoney,@type=chargetype from deleted
insert into newtable values(@cardid,@type,@money)
go
delete from transinfo where cardid ='10001001'
exec look
select * from newtable
--update触发器
exec look
alter trigger transinfo_trig_update on transinfo with encryption
for update
as
begin transaction
declare @cardid varchar(20),
@oldmoney money,
@newmoney money,
@type varchar(4)
select @cardid =cardid ,@newmoney=tmoney,@type=chargetype from inserted
select @oldmoney=tmoney from deleted
select @oldmoney , @newmoney
--if(@newmoney>20000)
--rollback transaction
if(@type='支取')
begin
if((@oldmoney - @newmoney)>20000)
begin
print'款额太高，更新不能完成！'
rollback transaction
end
else
begin
update bank set currentmoney = currentmoney - @newmoney where cardid=@cardid
commit transaction
end
end
else if(@type='存入')
begin
update bank set currentmoney = currentmoney + @newmoney where cardid=@cardid
end
else
begin
print'本次更新操作不能正常执行'
rollback transaction
end

--测试
update transinfo set cardid='10001000',chargetype='支取',tmoney=20001 where cardid='10001000'
exec look

update bank set currentmoney =100000 where cardid='10001000'

create trigger bank_trig_update on bank for update
as
declare @oldmoney money,
@newmoney money,
@cardid varchar(20)
select @oldmoney = currentmoney from deleted
select @newmoney = currentmoney from inserted
if((@oldmoney - @newmoney)>20000)
rollback transaction