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 <X64> (Build 9600: ) (Hypervisor) — Rick Brunken
– Rick Brunken, Commented Jun 16, 2015 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
– paparazzo, Commented Jun 16, 2015 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
– Rick Brunken, Commented Jun 17, 2015 at 7:06

SQLGobbleDeGook · Accepted Answer · 2015-06-16 17:30:46Z

1

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.

answered Jun 16, 2015 at 17:30

SQLGobbleDeGook

1561 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:34:45Z

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, 2017 at 12:34

CommunityBot

11 silver badge

answered Jun 16, 2015 at 15:20

MartianCodeHound

3801 silver badge8 bronze badges

1 Comment

Rick Brunken Over a year ago

I've tried this and the outcome is exactly the same as the original statement.

Collectives™ on Stack Overflow

SQL update inserted row with trigger

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related