请帮忙 看看我写的这个触发器错在哪里了
内容很简单，就是一个银行存取款的测试数据库。其中Depositor表记录储户信息，Access记录存取款信息，其中触发器建立在了Access表中，要求实现存取款的操作要相应更改Depositor表中的记录。Thrift表记录银行信息，在这个问题中和这个表没关系
但我测试了下，在Access表进行输入，实际没有任何反应。不知为何
MDF文件下载地址：
http://115.com/file/c2k78y85#
bank.mdf
LDF文件下载地址：
http://115.com/file/c2k7800b#
bank-Log.ldf
- SQL code
create trigger opera on Accessinstead of insertasbegin if exists (select 1 from inserted where mark='取') begin if exists (select 1 from inserted a inner join Depositor b on a.dno = b.dno and a.mark='取' and a.accessnum>b.remainder) begin print '该客户没有足够的金额可取' rollback tran end update Depositor set remainder = remainder - accessnum from Depositor a inner join inserted b on a.dno = b.dno and b.mark='取' end if exists (select 1 from inserted where mark='存' ) begin update Depositor set remainder = remainder + accessnum from Depositor a inner join inserted b on a.dno = b.dno and b.mark='存' end insert into Access select * from insertedend
------解决方案--------------------
- SQL code
create trigger opera on Accessinstead of insertasbegin if exists (select 1 from inserted where mark='取') begin if exists (select 1 from inserted a inner join Depositor b on a.dno = b.dno and a.mark='取' and a.accessnum>b.remainder) begin print '该客户没有足够的金额可取' rollback tran end else begin update Depositor set remainder = remainder - accessnum from Depositor a inner join inserted b on a.dno = b.dno and b.mark='取' end end if exists (select 1 from inserted where mark='存' ) begin update Depositor set remainder = remainder + accessnum from Depositor a inner join inserted b on a.dno = b.dno and b.mark='存' end insert into Access select * from insertedend
------解决方案--------------------
- SQL code
create trigger opera on Accessinstead of insertasbegin if exists (select 1 from inserted where mark='取') begin if exists (select 1 from inserted a inner join Depositor b on a.dno = b.dno and a.mark='取' and a.accessnum>b.remainder) begin print '该客户没有足够的金额可取' rollback tran end else begin update Depositor set remainder = remainder - accessnum from Depositor a inner join inserted b on a.dno = b.dno and b.mark='取' end end else if exists (select 1 from inserted where mark='存' ) begin update Depositor set remainder = remainder + accessnum from Depositor a inner join inserted b on a.dno = b.dno and b.mark='存' end insert into Access select * from insertedendtry