create trigger overdraft on account for update
as
if exists(select * from inserted where balance <0 )
begin
insert into borrower
select customer_name,depositor.account_number
from depositor, inserted
where inserted.account_number=depositor.account_number and inserted.balance<0
insert into loan
select account_number,branch_name, -balance
from inserted
where balance<0
update account set balance = 0
from account,inserted
where account.account_number = inserted.account_number and inserted.balance<0
end
这个触发器的意思是当balance中出现负值时就将account的account——number作为loan——number,
现在成功创建了trigger 可是执行这个句子update account set balance=-1992
where account_number='A-217'时
出现问题
(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)
消息 2627,级别 14,状态 1,过程 overdraft,第 9 行
违反了 PRIMARY KEY 约束 'PK_loan'。不能在对象 'dbo.loan' 中插入重复键。
语句已终止。我把loan中的主键去掉 就可以在loan中看到插入了2行重复数据~~为什么会插入两行?
as
if exists(select * from inserted where balance <0 )
begin
insert into borrower
select customer_name,depositor.account_number
from depositor, inserted
where inserted.account_number=depositor.account_number and inserted.balance<0
insert into loan
select account_number,branch_name, -balance
from inserted
where balance<0
update account set balance = 0
from account,inserted
where account.account_number = inserted.account_number and inserted.balance<0
end
这个触发器的意思是当balance中出现负值时就将account的account——number作为loan——number,
现在成功创建了trigger 可是执行这个句子update account set balance=-1992
where account_number='A-217'时
出现问题
(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)
消息 2627,级别 14,状态 1,过程 overdraft,第 9 行
违反了 PRIMARY KEY 约束 'PK_loan'。不能在对象 'dbo.loan' 中插入重复键。
语句已终止。我把loan中的主键去掉 就可以在loan中看到插入了2行重复数据~~为什么会插入两行?
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货