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
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.