SQL trigger - after insert,update wrong and correct values

Question

I have 3 tables:

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

What I am trying to accomplish is to delete from transaction when the total value is bigger that the balance of that person however it doesn't seem to work when I insert at the same time a wrong and a correct record. Why isn't working?

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

This is the wrong approach. First and foremost, your trigger should act as a gate-keeper. "Good" stuff is allowed, "bad" stuff generates an error. What it should not do is attempt to "replace" rows with "alternative" versions. In addition, you should not use the print statement in a production trigger - it is OK for temporary development and debugging but not in production. To help, please post DDL for the tables involved in a script that also populates those tables with sample data. (cont) — SMor, Dec 12 '18 at 14:02
And break your problem into parts that can be more easily developed. Choose a particular action (insert or update - insert is simpler) and write the code for that. Test, validate. Once that is working, then you attempt the update logic (which is almost always much more complicated). — SMor, Dec 12 '18 at 14:03
Lastly, it is time to learn proper tsql and best practices. No one should be using old-style joins, you should be using statement terminators, etc. — SMor, Dec 12 '18 at 14:05
One last comment. You don't replace any rows (by deleting and then inserting), you reject the attempt to insert/update bad rows by throwing an error (via throw or raiserror). Let the process executing the statement that caused the trigger to execute figure out what to do next. — SMor, Dec 12 '18 at 14:07

score 0 · Answer 1 · answered Dec 12 '18 at 14:23

I'm a little surprised that this doesn't throw a syntax error, but this bit is wrong:

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)

Because you aren't joining inserted to transaction so the DELETE isn't correlated to the FROM.

SQL trigger - after insert,update wrong and correct values

1 Answers1