CODEWITHSUNDEEP
sqlsql-serverdatabasetriggers
enzhou.liu
enzhou.liu

Reputation: 183

SQL Server INSERT trigger does not work

I have a trigger to copy over the data from Table A to table B when table A is changed

The trigger is like this :

ALTER TRIGGER ATrigger 
ON A AFTER INSERT, DELETE, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    DELETE FROM B WHERE id IN (SELECT id FROM deleted)

    INSERT INTO B(Id, col1,col2) (SELECT i.Id, i.col1, i.col2 FROM inserted i)
END

But i see not all the data inserted in A are copied to B, the data copied seems very random I was searching around, found it might caused by multi-insert, someone is suggesting using cusor, but i think for mine, it should be ok to insert or delete from the inserted, deleted table using this two sql.

Please advise, thanks!

Upvotes: 0

Views: 608

Answers (1)

Kenneth Fisher
Kenneth Fisher

Reputation: 3812

I'm not certain this is your problem but your trigger has 2 "gotchas". First on an insert the deleted table will have no rows in it so no deletes will be done. Second is the reverse and potentially your problem. On a delete the inserted table has no rows. So all of the IDs are going to be deleted from table B but not re-inserted. On top of this if ID is not a unique key for table A then when you insert a second copy of it you will be deleting all of your history in table B and only adding the "new" history.

If you can provide more information on the structure of the 2 tables and the purpose of the trigger, not to mention any patterns on the rows being inserted or not being inserted as the case may be we can be of more help.

Upvotes: 1

Related Questions