sql-server - SQL 触发器 - 插入后，更新错误和正确的值

Question

我有 3 张桌子：

transaction(IDtransaction,IDperson,IDPos,IDStore,TotalValue,Date)
items(IDtransaction,IDItem,IDproduct,Quantity)
persons(IDperson, balance)

我想要完成的是当总价值大于该人的余额时从交易中删除，但是当我同时插入错误和正确的记录时它似乎不起作用。为什么不工作？

create trigger trigger4
on transaction
After insert,update
As
BEGIN   

delete transaction
FROM inserted i
where transaction.IDtransaction=i.IDtransaction AND EXISTS (select * from inserted i, persons p
                where i.IDperson=p.IDperson 
                and i.TotalValue > p.balance) 

if @@ROWCOUNT > 0
begin
print('Error!')
    if exists(select 1 from deleted) and exists (select 1 from inserted) --check if it is an update
    begin
    SET IDENTITY_INSERT transaction ON

    insert into transaction(IDtransaction,IDperson,IDPos,IDStore,TotalValue,Date)
    select d.IDtransaction,d.IDperson,d.IDPos,d.IDStore,d.TotalValue,d.Date
    from deleted d
    where d.IDtransaction not in (select t.IDtransaction from transaction t)

    SET IDENTITY_INSERT transaction OFF

    end
end             
END

标签： sql-servertsqldatabase-trigger

Answer 1

我有点惊讶这不会引发语法错误，但这一点是错误的：

delete transaction
FROM inserted i
where transaction.IDtransaction=i.IDtransaction AND EXISTS (select * from inserted i, persons p
                where i.IDperson=p.IDperson 
                and i.TotalValue > p.balance) 

因为您没有加入inserted，transaction所以 DELETE 与 FROM 无关。