December 15, 2012 at 8:31 pm
Hello All,
I have created 2 After update triggers on a table which is basically used to fire a sql server job.The triggers are designed in such a way that,it will call these jobs only if a particluar value(For e.g end date gets updated) in the table gets updated.
I have scenario where the second trigger is getting fired whenever there is an update in the table.Though it is not the same in the first case.
Why the second trigger is getting firing even after specifying the required conditions inside the trigger.?
Your help would be highly appreciated.
December 15, 2012 at 9:06 pm
You are very aware of your problem, but without the table definition, the updating TSQL, and the trigger definitions. It is very, very difficult for those who wish to help you to be of assistance.
Please read the first link in my signature block to learn a quick and easy way to post the information requested (Table definition, TSQL, trigger definition)
December 15, 2012 at 9:27 pm
Hi Ron,
Thanks for your reply.Please see the details below
--Table 1
CREATE TABLE PROCESS
(NAME [varchar](50) NOT NULL,
[PROCESS_END_DT] [datetime] NOT NULL)
---Trigger 1
CREATE TRIGGER [dbo].[PROCESS_1]
ON [dbo].[PROCESS]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
SETXACT_ABORT ON;
DECLARE @SQL_JOB_NAME VARCHAR(MAX)
DECLARE @NAMEVARCHAR(50),
@PROCESS_END_DATEDATETIME
DECLARE @job_id uniqueidentifier, @running int
set @running = 0
CREATE TABLE #ENUM_JOB (
Job_ID uniqueidentifier,
Last_Run_Date int,
Last_Run_Time int,
Next_Run_Date int,
Next_Run_Time int,
Next_Run_Schedule_ID int,
Requested_To_Run int,
Request_Source int,
Request_Source_ID varchar(100),
Running int,
Current_Step int,
Current_Retry_Attempt int,
State int
)
SELECT@NAME = IPU.NAME,@PROCESS_END_DATE = IPU.Process_End_Dt
FROMPROCESSIPU INNER JOIN
PROCESS_LAST_RUN LRD ON LRD.NAME = IPU.NAME
WHEREIPU.Process_End_Dt > LRD.LAST_RUN_DATE AND IPU.NAME IN ('BOB')
IF @NAME IN ('BOB')
BEGIN
SELECT @job_id=job_id FROM [msdb].[dbo].[sysjobs]
WHERE name=N'Job1'
TRUNCATE TABLE #enum_job
INSERT INTO #enum_job
EXEC master.dbo.xp_sqlagent_enum_jobs 1, sa, @job_id
select @running = running from #enum_job
if @running = 0
BEGIN
SET @SQL_JOB_NAME = 'Job1'
EXEC MSDB.DBO.SP_START_JOB @JOB_NAME =@SQL_JOB_NAME
END
END
END
---Trigger2
create TRIGGER [dbo].[PROCESS_2]
ON [dbo].[PROCESS]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
SETXACT_ABORT ON;
DECLARE @SQL_JOB_NAME VARCHAR(MAX)
DECLARE @NAMEVARCHAR(50),
@PROCESS_END_DATEDATETIME
DECLARE @job_id uniqueidentifier, @running int
set @running = 0
CREATE TABLE #ENUM_JOB (
Job_ID uniqueidentifier,
Last_Run_Date int,
Last_Run_Time int,
Next_Run_Date int,
Next_Run_Time int,
Next_Run_Schedule_ID int,
Requested_To_Run int,
Request_Source int,
Request_Source_ID varchar(100),
Running int,
Current_Step int,
Current_Retry_Attempt int,
State int
)
SELECT@NAME = IPU.NAME,@PROCESS_END_DATE = IPU.Process_End_Dt
FROMPROCESSIPU INNER JOIN
PROCESS_LAST_RUN LRD ON LRD.NAME = IPU.NAME
WHEREIPU.Process_End_Dt > LRD.LAST_RUN_DATE AND IPU.NAME IN ('CAT')
IF @NAME IN ('CAT')
BEGIN
SELECT @job_id=job_id FROM [msdb].[dbo].[sysjobs]
WHERE name=N'Job2'
TRUNCATE TABLE #enum_job
INSERT INTO #enum_job
EXEC master.dbo.xp_sqlagent_enum_jobs 1, sa, @job_id
select @running = running from #enum_job
if @running = 0
BEGIN
SET @SQL_JOB_NAME = 'Job2'
EXEC MSDB.DBO.SP_START_JOB @JOB_NAME =@SQL_JOB_NAME
END
END
END
GO
Iam still wondering why the second trigger is getting fired whenever there is any update on the table.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply