SQL Trigger output

Question

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?

Note that when you insert a second row, both of them will have `Name` set to `'Rob Stanfield'` since you have no `WHERE` clause on your `UPDATE`. And your trigger is only going to output 1 (random) name from `inserted` if you insert multiple rows in a single `INSERT`. And you shouldn't (in general) produce any output from a trigger. — Damien_The_Unbeliever, Oct 03 '12 at 13:51

Bridge · Accepted Answer · 2012-10-03T13:46:28.587

3

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?

edited Oct 03 '12 at 13:46

answered Oct 03 '12 at 13:39

Bridge

29,818
9
60
82

And yes, as Damien points out, this is assuming there is only one row at any given time, you need a where clause on your select statement in the trigger. But then again, it's not entirely clearly what you're trying to achieve in the first place. – Bridge Oct 03 '12 at 14:32
Thanks. I am just trying to learn about Triggers as I don't really use them and probably should. I realise there should be a filter where clause. +1 for the statement: " before your trigger ran". – w0051977 Oct 03 '12 at 18:33
can you answer the question in my last comment before I mark your answer as the correct one? – w0051977 Oct 07 '12 at 18:43
@w0051977 Sorry - which comment do you mean? – Bridge Oct 07 '12 at 20:59

SQL Trigger output

1 Answers1