Help for Trigger

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

ThierryOn 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)

Help - Can Execute return a value?

Hello
I'm relatively new to SQL Server/T-SQL and find myself stuck with this
problem:
I need to do something like this:
Declare @.someValue NVarchar(100)
Declare @.someFunction Varchar(100)
--
-- Assign value to @.someValue from a Cursor
--
--
-- Assign name of the function to @.someFunction from a Cursor
--
Declare @.ret NVarchar(100)
Execute 'Select @.ret = ' + @.someFunc + '(''' + @.someValue + ''')'
I expect the last Execute statement to leave the return value from the
function in @.ret.
What I get is
Must declare the variable '@.ret'.
I have also tried
Execute sp_ExecuteSQL 'Select @.ret = ' + @.someFunc + '(''' +
@.someValue + ''')'
with the same result.
Any help with making this work or other ways of doing this will be very
much appreciated!
TIA.
Vamsi.Vamsi,
Use sp_executesql.
Declare @.ret NVarchar(100)
declare @.sql nvarchar(4000)
set @.sql = N'Select @.ret = ' + @.someFunc + '(''' + @.someValue + ''')'
exec sp_executesql @.sql, N'@.ret NVarchar(100) output', @.ret output
print @.ret
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB|||Splendid!!
I still can't understand that one line of code, but, it worked
perfectly :-)
Thanks very much, AMB.
V.|||tvamsidhar (tvamsidhar@.gmail.com) writes:
> Splendid!!
> I still can't understand that one line of code, but, it worked
> perfectly :-)
For more details on sp_executesql and dynamic SQL in general, see
an article on my web site: http://www.sommarskog.se/dynamic_sql.html.
By the way, if you are new to T-SQL, dynamic SQL is probably not where
you should start.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland, I'll study the article! Its already in my Google
bookmarks :)
As for starting with dynamic SQL, I really don't have a choice :-) I
need to be able to run validations and formatting on type-less text
data that should be "interpreted" to be of datatypes defined in a
metadata repository (SQL Server tables) and satisfying validations
(reg. expressions, TSQL functions/SPs, etc) specified in the same
repository.
Running these on the app. server turned out to be way too inefficient
and cumbursome; hence the dynamic SQL. And although I'm more
experienced with PL/SQL, the powers that be insist on using SQL Server
I thank you for the input.
V.