CODEWITHSUNDEEP
sqltriggers
w0051977
w0051977

Reputation: 15807

SQL Trigger output

I execute the following trigger:

ALTER TRIGGER [dbo].[TestTrigger] ON [dbo].[TestTable] 
after update as
    Declare @Name As varchar(30)
    update TestTable set [Name] = 'Rob Stanfield'
    select @Name = [Name] from inserted
    print @Name
GO

The insert statement I am executing is: 

INSERT INTO TestTable (name) VALUES ('Ed Watkins')

The output is as follows:

(1 row(s) affected)
Ed Watkins
(1 row(s) affected)

I expect the output to be:

(1 row(s) affected)
Rob Stanfield

(1 row(s) affected)

What am I missing?

Upvotes: 1

Views: 3994

Answers (1)

Bridge
Bridge

Reputation: 30651

Inserted will be a copy of the rows changed in TestTable before your trigger ran, so the value for name in it will still be Ed Watkins.

I'm not sure why you're trying to do this, but you'd have to query the table you just updated to get the new value you just updated to in the trigger:

ALTER TRIGGER [dbo].[TestTrigger] ON [dbo].[TestTable] 
after update as
    Declare @Name As varchar(30)
    update TestTable set [Name] = 'Rob Stanfield'
    select @Name = [Name] from TestTable 
    print @Name
GO

Were you really looking for an instead of insert trigger?

Upvotes: 3

Related Questions