SQL update inserted row with trigger

Question

I'm trying to update a just inserted row in SQL with a table trigger. Below is the trigger. It seems this only updates the already inserted rows and NOT the just inserted row. What am I doing wrong?

ALTER TRIGGER [dbo].[TRG_DIM_Employee]
ON [dbo].[DIM_Employee]
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON
    UPDATE DIM_Employee
    SET ParentEmployeeKey = i.EmployeeKey
    FROM INSERTED i
    WHERE DIM_Employee.EmployeeId = i.EmployeeId
END

Microsoft SQL Server 2012 - 11.0.5556.0 (X64) Oct 31 2014 16:50:24 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor) — Rick Brunken, Jun 16 '15 at 15:17
Why not just have a default value in SQL? You want a trigger of overwrite even if they supply a value for ParentEmployeeKey? — paparazzo, Jun 16 '15 at 15:51
Because not only the just inserted row must be changed but all rows having that particular EmployeeId must be updated. — Rick Brunken, Jun 17 '15 at 07:06
Possible duplicate of [Trigger that updates just the inserted row](http://stackoverflow.com/questions/8987097/trigger-that-updates-just-the-inserted-row) — Liam, May 22 '17 at 10:34

score 1 · Answer 1 · answered Jun 16 '15 at 17:30

As far as I can recognize, this is old join syntax but is functionally equivalent. I set up a quick test but can't repro this issue, seems to work as expected.

FYI you should move to using proper JOIN syntax, this old ANSI-89 standard is already discontinued in some forms in SQL 2012.

score 0 · Answer 2 · edited May 23 '17 at 12:34

0

Your update statement is not quite right. You need to JOIN your table with the inserted table rows.

ClientID int,
ALTER TRIGGER [dbo].[TRG_DIM_Employee]
ON [dbo].[DIM_Employee]
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON

    UPDATE emp
    SET emp.ParentEmployeeKey = i.EmployeeKey
    FROM  DIM_Employee emp
    JOIN INSERTED i on emp.EmployeeId = i.EmployeeId
END

More examples of doing an update join here.

edited May 23 '17 at 12:34

Community

1
1

answered Jun 16 '15 at 15:20

MartianCodeHound

355
1
8

I've tried this and the outcome is exactly the same as the original statement. – Rick Brunken Jun 17 '15 at 07:04

SQL update inserted row with trigger

2 Answers2