CODEWITHSUNDEEP
sql-serversql-server-2008
Somashekhar
Somashekhar

Reputation: 513

How to get updating row ID in SQL Server trigger

I have a table Employee

  Emp_Id  Name
  1       XYZ
  2       ABC

and another table DivisionInfo

  DivisionID  Division  Emp_Id
       1       mmm          1
       2       nnnn         1
       3       oooo         1

I need to write a trigger to rollback updates of the Employee table, if the Emp_Id is present in the DivisionInfo table. In this case Emp_Id = 1.

How to get the row id of the row being updated in the SQL trigger?

This is my trigger code:

ALTER TRIGGER [dbo].[EmployeeTrigger]
ON [dbo].[Employee]
FOR UPDATE 
AS
   IF((SELECT COUNT([DivisionID ]) 
       FROM [DivisionInfo] AS D 
       INNER JOIN Employee AS E ON D.[Emp_Id] = E.Emp_Id
       WHERE D.[Emp_Id] = E.Emp_Id) > 0)
   BEGIN
      RAISERROR ('Testing', 10, 1); 
      ROLLBACK TRANSACTION
      RETURN
   END

Here it's always true

Upvotes: 2

Views: 3843

Answers (1)

Andrey Morozov
Andrey Morozov

Reputation: 7969

Try this:

ALTER TRIGGER [dbo].[EmployeeTrigger]
ON [dbo].[Employee]
FOR UPDATE 
AS
IF((
    SELECT COUNT([DivisionID ])
    FROM [DivisionInfo] D
    JOIN INSERTED I ON I.[Emp_Id] = D.[Emp_Id])>0)
BEGIN
        RAISERROR ('Testing', 10, 1); 
     ROLLBACK TRANSACTION
     RETURN
END

Upvotes: 1

Related Questions