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