how to print the newly created value in the trigger in RAISERROR statement

above trigger will fire before inserting into EMPJOBS to check is there any record exists based on above parametes in the last minute.if any records exists it will throw error.if(@COUNT > 0)that means already records exists with those parameters.i wwant to print the newly created emp_job_id in   IF @COUNT > 0 statement.
how to print newly inserted emp_job_id(i.emp_job_id).
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

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.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>i want to print the newly created emp_job_id in IF @COUNT > 0 statement.
Might not be a bad idea to INSERT these into some kind of log table for future action.
0
chaitu chaituAuthor Commented:
how to print these values in log values as well as in below statement..

RAISERROR ('JOB ALREADY RUNNING' || i.emp_job_id, 16, 1)
0
ste5anSenior DeveloperCommented:
You can return a value by using the format options of RAISERROR:

RAISERROR (N'This is message %s %d.', 16, 1, N'number', 5);

Open in new window


And in your trigger e.g. as

DECLARE @RunningJobID INT = NULL;

SELECT  @RunningJobID = ej.emp_job_id
FROM    EMPJOBS ej
        INNER 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 NOT @RunningJobID IS NULL 
    BEGIN
        ROLLBACK TRANSACTION;
        RAISERROR (N'Job %d currently running.', 16, 1, @RunningJobID);
    END;

Open in new window


But instead of using a trigger to ensure data integrity, You should modify your data model.
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

chaitu chaituAuthor Commented:
i need to print which will be created newly .i think need to change like this.

SELECT  @RunningJobID = i.emp_job_id
0
ste5anSenior DeveloperCommented:
You are rolling back the transaction, what should this information be used for?

DECLARE @NewID INT;

SELECT  @NewID = emp_job_id
FROM    INSERTED;

Open in new window


Caveat: You are currently assuming that there is only one job inserted at a time. This is a problem as triggers are executed per statement. Thus it may work on more than one row.
0
chaitu chaituAuthor Commented:
i want to log the job_ids which are missed while inserting into jobs table.
0
ste5anSenior DeveloperCommented:
Please rephrase your question and explain the entire logic behind your code. Otherwise this makes no sense.
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.