Click here to Skip to main content
14,872,753 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL

Hi,

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.

SQL
USE [Demo]
GO
/****** Object:  Trigger [dbo].[CheckJobExecutionError]    Script Date: 10/10/2013 1:50:49 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Steve Van Lint>
-- Create date: <06/09/2013>
-- Description:	<Write a record to Error_Capture db to monitor Batch Jobs>
-- =============================================
ALTER TRIGGER [dbo].[CheckJobExecutionError] 
   ON  [dbo].[DM_JOB] 
   WITH EXECUTE AS 'Error_Capture_user'
   AFTER UPDATE
AS

-- Declare and retrieve the instance name and URL from ArcoInfo
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');

-- Check if there is already an entry for the Job ID that changed
IF (SELECT count(*) FROM Error_Capture.dbo.Capture, inserted WHERE Client = @Client and Error_Capture.dbo.Capture.Job_id = inserted.JOB_ID) > 0
	BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    -- If the Job Status goes to idle or in progress, delete the record from the Error Capture table

		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)
		-- If not, update the existing record in the Error Caputre table
		ELSE
			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)
	END

-- If there is no record already present in the Error Capture table
ELSE
	BEGIN	
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    -- Insert the record in the Error Capture table	
		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)
	END;
Posted

1 solution

Given below is the code for a simple trigger which will inserts the rows in Temp Table if no matching records exists or updates if record already exists. This Trigger will work for single as well as multiple rows updation -

SQL
CREATE TRIGGER TEST
ON EMPLOYEE
AFTER UPDATE
AS

BEGIN

--- Query for Inserting New Rows
	INSERT INTO TEMP
		(ID,NAME)
	SELECT ID, NAME FROM INSERTED WHERE NOT NOT EXISTS
		(SELECT * FROM TEMP WHERE TEMP.ID=INSERTED.ID)		

--- For Updating Existing Rows
	UPDATE TEMP SET
		ID=INSERTED.ID
		,NAME=INSERTED.NAME
	FROM TEMP
		INNER JOIN INSERTED ON INSERTED.ID=TEMP.ID		
				
END
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900