How can I set more than one condition after update statement?

I am trying to change the IsActive status to 0 if any of the other columns get deleted. I wrote this query, but when I run it I get this error:

Msg 207, Level 16, State 1, Procedure emp_Delete, Line 11 [Batch Start Line 54]
Invalid column name ’empname’.

Msg 4104, Level 16, State 1, Procedure emp_Delete, Line 11 [Batch Start Line 54]
The multi-part identifier “deleted.empname” could not be bound.

Query:

CREATE TRIGGER emp_Delete
ON [dbo].[Emp_triggers]
AFTER DELETE
AS
    DECLARE @IsActive smallint
    SET @IsActive = 0

    UPDATE [dbo].[EmpHistory] 
    SET [IsActive] = @IsActive
    WHERE [empid] = deleted.[empid] 
       OR [deptid] = deleted.[deptid] 
       OR [empname] = deleted.[empname]
GO

Answer

You can achieve the Same by using JOIN Condition

UPDATE Z
SET [IsActive] = @IsActive
FROM [dbo].[EmpHistory] Z
INNER JOIN deleted ON (Z.[empid] = deleted.[empid] OR  Z.[deptid] = deleted.[deptid] OR 
Z.[empname] = deleted.[empname])

Advertisement

Answer