our application is running on multiple nodes and having concurrency issues.we have written below trigger to handle the concurrency issues. before inserting into EMPJOBS whether any jobs that are running at the last 1 minute on this @EMP_TYPE,@EMP_ID, @STORE_ID parameters .if we get count that means already same record that are currently running on the EMPJOBS table.
after this trigger is enabled even below statement is executed but records are not showing up.
insert into EMPJOBS values(1,'ddd',1111',getda
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
CREATE TRIGGER [dbo].[EMPJOBS_Trigger] ON XXX.DBO.EMPJOBS
INSTEAD OF INSERT
DECLARE @JOB_TYPE nvarchar
DECLARE @EMP_ID int
DECLARE @STORE_ID int
DECLARE @START_DATE date
DECLARE @COUNT int
SET @COUNT = (SELECT count(*) FROM EMPJOBS WHERE
EMP_TYPE= @EMP_TYPE and
START_DATE >= DATEADD(mi, -1, GETDATE()))
IF @COUNT > 0
PRINT 'JOB ALREADY RUNNING'
RAISERROR ('JOB ALREADY RUNNING', 16, 1)
PRINT 'CREATE JOB'