question

David 2 1 avatar image
David 2 1 asked

ON DELETE Trigger Not Firing

Hi there, I'm trying to create a trigger on a table that will log the row(s) deleted into a separate table when a user issues the DELETE command. So far the it's not logging any rows into the separate table. ALTER TRIGGER Trg_Forms_Deleted ON Forms FOR DELETE AS BEGIN INSERT INTO Forms_Deleted( [ID], [FormID], [Notes]) SELECT d.[ID], d.[FormID], d.[Notes] FROM deleted d JOIN Forms t ON d.ID = t.ID END Can anyone help? TIA
tsqltriggerdelete
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered
By default, a trigger will fire AFTER the event. So when you then join back to Forms with Forms.ID = deleted.id no rows will be found. Simply make your trigger INSERT INTO Forms_Deleted( [ID], [FormID], [Notes]) SELECT d.[ID], d.[FormID], d.[Notes] FROM deleted d
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

OK. You can't stop the trigger firing, but what you can do is add conditional logic to the body of the trigger - if exists (select ID from deleted) begin .... end
1 Like 1 ·
Many thanks Kev. The issue appears to be the user application sending a delete statement for a non-existent ID, such as DELETE FROM Forms WHERE ID = 5. When there is no ID of 5 therefore nothing is inserted into the Forms_Deleted table however the trigger stills fires. Is it possible to make the trigger ONLY fire when a valid ID is actually deleted?
0 Likes 0 ·
Fantastic Kev. Have a great day.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.