SQLTeam.com | Weblogs | Forums

Update a value in another table by after insert trigger

#1

Hi

suppose I have this two tables:

Holidays table:

CREATE TABLE [dbo].[Holidays](
[Id] [int] NOT NULL,
[EmpId] [int] NOT NULL,
[SDate] [datetime2](7) NULL,
[EDate] [datetime2](7) NULL,
[ReturnStatus] [bit] NULL)

Employees:

CREATE TABLE [dbo].[Employees](
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmpName] [nvarchar](50) NOT NULL,
[DeptId] [int] NULL,
[StaffId] [int] NULL)

I want to update the StaffId value in Employees table to the value '2' after insert of Holidays table where the new Holidays.EmpId of inserted record = Employees.Id

I tried this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TRG_HolidaysAftIns]
ON [dbo].[Holidays]
AFTER INSERT AS
BEGIN
UPDATE Employees set StaffId = 2
FROM inserted
WHERE EmployeeId = inserted.id
END
0 Likes

#2

I think you used the wrong column name on the join to inserted (id vs empid).

Personally I'd use this style for that type of UPDATE:

UPDATE E
SET StaffId = 2
FROM employees E
WHERE EXISTS(SELECT 1 FROM inserted i WHERE E.EmployeeId = i.empid)

0 Likes