I have a question about triggers on SQL.
On 1 servers we have multiple databases where I want to install a trigger that will check on UPDATE statements and write a record in a seperate dedicated table whenever a record in the triggered database is updated to a status 2 or 3.
The trigger I created works but I did not take into account multirow updates. So basically my trigger operates on the assumption that only 1 record is updated at a time.
I have already removed some declarations that obviously limit this functionality but I am still stuck and would appreciate some assistance.
Basically I check first to see if the record already exists in the destination db and update if it does, otherwise I insert a new record. As you may expect, the job_id is unique. In the destination db, the Client and job_id are PK but allow duplicates.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER TRIGGER [dbo].[CheckJobExecutionError]
WITH EXECUTE AS 'Error_Capture_user'
DECLARE @Client as varchar(200);
SET @Client = (SELECT PARMVALUE from ARCOINFO where PARMNAME = 'ApplicationTitle');
DECLARE @URL as varchar(200);
SET @URL = (SELECT PARMVALUE from ARCOINFO where PARMNAME = 'url' AND CATEGORY= 'DOMA');
IF (SELECT count(*) FROM Error_Capture.dbo.Capture, inserted WHERE Client = @Client and Error_Capture.dbo.Capture.Job_id = inserted.JOB_ID) > 0
SET NOCOUNT ON;
IF (SELECT JOB_STATUS FROM inserted) in (0, 1)
DELETE FROM Error_Capture.dbo.Capture WHERE Client = @Client AND Job_id = (SELECT JOB_ID FROM inserted)
UPDATE Error_Capture.dbo.Capture SET Status = (SELECT JOB_STATUS FROM inserted), Time = GETDATE(), Error = (SELECT JOB_LASTERROR FROM inserted) WHERE Client = @Client and Job_id = (SELECT JOB_ID FROM inserted)
SET NOCOUNT ON;
INSERT INTO Error_Capture.dbo.Capture (Client, Job_id, Job_Name, Status, Time, Error)
SELECT @Client, inserted.JOB_ID, inserted.JOB_NAME, inserted.JOB_STATUS, GETDATE(), inserted.JOB_LASTERROR
FROM inserted, deleted
WHERE inserted.JOB_STATUS in (2,3) or deleted.JOB_STATUS in (2,3)