SQLTeam.com | Weblogs | Forums

Execute trigger on insert failure

#1

I have a trigger on a table that enters an event into another table when rows are inserted and it increments a number in another table. How can I alter the trigger so that it will also enter an event into the log table if the rows are not inserted because the stored procedure fails? I should also not increment the number on failure.

Also, I am wondering how on successful insert I can have it write the procedure name and the number of records to the log table.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[myTrigger]
   ON  [dbo].[myTableA]
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
	INSERT INTO myLog
(Date, TaskName, Status)
VALUES
(getdate(), 'string', 'string')

UPDATE  MyTableB
SET NUM = NUM + 1

END
#2

you can't have a trigger fire on error. use try/catch for that

#3

The trigger has no way of determining the proc that caused it to be fired, and you can't directly pass the proc name to the trigger. You could, however, either (a) write the proc name to a table somewhere for the trigger to read it or (b) put the object_id of the calling proc into CONTEXT_INFO and let the trigger pull it from there.

#4

Thanks Scott. I did end up writing the procedure name to a table and used it to update my log.

#5

What if this is heavy mutli-transaction? How do you match the proc name to the row inserted?

#6

You could use session_id (@@SPID from within the trigger) or write the lookup key for the table to CONTEXT_INFO for the trigger to read.

#7

that;s what I was getting at. Hoping the OP did something like that