By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,276 Members | 1,951 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,276 IT Pros & Developers. It's quick & easy.

Help for Trigger

P: n/a

Hello

I would like to get the content of a field based in the field Name.
Suppose a table with a field Named 'LastName' for wich there is a trigger
after update
I store the field name in a local variable

Set @ColName = 'LastName'

How can I retrieve the value of the @ColName from the inserted table using
the @Colname variable ?

I tried this:

Set @Cmd = 'DECLARE @DataValue varchar(100) Set @DataValue = (Select i.' +
@ColName + ' from inserted i) print @DataValue'
exec (@Cmd)

print @Cmd gives

DECLARE @DataValue varchar(100) Set @DataValue = (Select i.LastName from
inserted i) print @DataValue

But I got the following error

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'inserted'.

Any idea why ?

Thanks for your help

Thierry

Jul 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On Fri, 23 Apr 2004 20:57:10 +0200, Thierry Marneffe wrote:

Hello

I would like to get the content of a field based in the field Name.
Suppose a table with a field Named 'LastName' for wich there is a trigger
after update
I store the field name in a local variable

Set @ColName = 'LastName'

How can I retrieve the value of the @ColName from the inserted table using
the @Colname variable ?

I tried this:

Set @Cmd = 'DECLARE @DataValue varchar(100) Set @DataValue = (Select i.' +
@ColName + ' from inserted i) print @DataValue'
exec (@Cmd)

print @Cmd gives

DECLARE @DataValue varchar(100) Set @DataValue = (Select i.LastName from
inserted i) print @DataValue

But I got the following error

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'inserted'.

Any idea why ?

Thanks for your help

Thierry


Hi Thierry,

The inserted and deleted pseudo-tables can only be used in the
trigger. Invoking dynamic SQL creates a new environment, so you can't
use the inserted and deleted tables there.

If you don't use dynamic SQL, all will be swell:

CREATE TRIGGER TestIt
ON MyTable AFTER UPDATE
AS
DECLARE @DataValue varchar(100)
SET @DataValue =
(SELECT LastName
FROM inserted)
PRINT @DataValue
go

Of course, this trigger will still result in an error if you perform
an update that affects more than one row - always remember that a
trigger is fired exactly once for each update statement, regardless of
the number of rows that match the search criteria (can be anything
from 0 up to the complete table) and regardless of whether the data
was actually chaged or not (ie UPDATE MyTable SET MyColumn = MyColumn
will fire the trigger and will have the complete table in the inserted
ande deleted pseudo-tables).
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.