Help ! How do i refer to the deleted table into a trigger statement ?

Hi there,

Could somebody post some simple example how one can refer to
a column in the 'deleted' temporary table within a trigger definition ?
Should the 'deleted' and 'inserted' temp tables be declared, or they are
'implied' ?

I keep having this msg when trying to create a trigger
"The column prefix 'deleted' does not match with a table name or alias name
used in the query"

Thanks for your inputs,
Eddy.[posted and mailed, please reply in news]

Eddy Fontaine (eddy.fontaine@.advalvas.be) writes:
> Could somebody post some simple example how one can refer to
> a column in the 'deleted' temporary table within a trigger definition ?
> Should the 'deleted' and 'inserted' temp tables be declared, or they are
> 'implied' ?

They are implied. You just use them.

> I keep having this msg when trying to create a trigger
> "The column prefix 'deleted' does not match with a table name or alias
> name used in the query"

May I guess that you are using dynamic SQL? Code executed in EXEC() or
sp_executesql is not part of the trigger, but constitutes a scope on
its own.

A practical workaround is to say:

SELECT * INTO #deleted FROM deleted
SELECT * INTO #inserted FROM inserted

first in the trigger.

This can even be useful in a trigger that does not use dynamic SQL,
particular if multi-row operations are common, because you can get
better performance. The virtual tables inserted and deleted are
not very efficient when they get some size.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Eddy Fontaine (eddy.fontaine@.advalvas.be) writes:
> Thanks Erland,
> I was trying to refer to a column in the deleted temp table with
> 'deleted.column_name'...
> My sql server does not like this !

Since you never posted the code to the trigger that gave you the
error message, I cannot say what your problem really was. Judging from
the code you post now, may you tried with:

> UPDATE DemographicData SET Lastupdate = getdate()
> WHERE ID = deleted.ID

which is not legal, but that has nothing to do with inserted/deleted,
but with the fact that you must name a table in a query in a FROM
clause or after INSERT/DELETE/UPDATE.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp