on before trigger is not working

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',getdate());
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE TRIGGER [dbo].[EMPJOBS_Trigger] ON XXX.DBO.EMPJOBS
INSTEAD OF INSERT 
AS
BEGIN
  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 
        EMP_ID=@EMP_ID and 
        STORE_ID=@STORE_ID and 
        START_DATE >= DATEADD(mi, -1, GETDATE()))


  IF @COUNT > 0
  BEGIN
  PRINT 'JOB ALREADY RUNNING'
  RAISERROR ('JOB ALREADY RUNNING', 16, 1)
  --ROLLBACK TRANSACTION
  RETURN 
  END
  ELSE
  PRINT 'CREATE JOB'
  END
GO

Open in new window

LVL 20
chaitu chaituAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
your code is written in a way it assumes the data goes automatically into the variables ...
but this is not the case.

it should not be INSTEAD OF INSERT, but ON INSERT, OR you have to code in the final INSERT.

CREATE TRIGGER [dbo].[EMPJOBS_Trigger] ON XXX.DBO.EMPJOBS
FOR INSERT
AS
BEGIN

  SET @COUNT =  (SELECT count(*) 
      FROM EMPJOBS ej
      JOIN INSERTED i
        ON ej.EMP_TYPE= i.EMP_TYPE 
        AND ej.EMP_ID= i.EMP_ID  
        AND ej.STORE_ID= i.STORE_ID 
        AND ej.START_DATE >= DATEADD(mi, -1, GETDATE())
        AND ej.emp_job_id <> i.emp_job_id
           )

  IF @COUNT > 0
  BEGIN
    PRINT 'JOB ALREADY RUNNING'
    RAISERROR ('JOB ALREADY RUNNING', 16, 1)
    --ROLLBACK TRANSACTION
  END

END 
GO 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
UnifiedISCommented:
How do these variables get populated?
 DECLARE @JOB_TYPE nvarchar
  DECLARE @EMP_ID int
  DECLARE @STORE_ID int
  DECLARE @START_DATE date
  DECLARE @COUNT int
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.