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
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
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
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?
18 People are following this question.
