Cant see the 'inserted' table from within a trigger

[stiej]

hi,

I have this inside my trigger

EXEC ('DECLARE @PKValue varchar(255) SELECT @PKValue = ' + @PKField + ' from inserted')

Though the trigger does not execute despite that it parses okay.

Is it not possible to view the inserted or deleted tables inside a trigger when the SQL to do so is executed from within an EXEC statement?

The error I get is "'Invalid object name inserted"

Thanks.

 

[druer]

EXEC is done in a different context and the INSERTED and DELETED tables are only visible with the context of the TRIGGER while it is being executed.

Why are you trying to utilzie the EXEC for what you are doing? That could be done directly in the trigger.

 

[stiej]

well, due to our auditing method, i require to caoncateneate all the primary keys in the trigger table. in an attempt to generalise the trigger, to limit any hardcoding of field names, i've used sp_pkeys within the trigger to put the primary key fieldnames into a temp table then a cursor to loop through them and get their values from inserted or from deleted. perhaps my code sample will explain better....
CREATE TRIGGER TRG_UPDATEsjTest ON tbl_sjTest FOR UPDATE AS

DECLARE @TableName varchar(255)
DECLARE @PKField varchar(255)
DECLARE @PKValue varchar(255)
DECLARE @Str varchar(255)
DECLARE @SQLStr varchar(1024)
DECLARE @JoinStr varchar(1024)

SET @TableName = 'tbl_sjTest'
SET @JoinStr = ''
SET @PKValue = ''

-- Find Primary Key column/s
CREATE TABLE #t
(
Table_Qualifier varchar(255),
Table_Owner varchar(255),
Table_Name varchar(255),
PKField varchar(255),
Key_Sequence int,
PK_Name varchar(255)
)
INSERT INTO #t EXEC sp_pkeys @TableName

-- Fetch Primary Key column/s values and construct JOIN syntax for next cursor
DECLARE c CURSOR FAST_FORWARD FOR
SELECT PKField
FROM #t
OPEN c
FETCH NEXT FROM c INTO @PKField
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('DECLARE @PKValue varchar(255) SELECT @PKValue = ' + @PKField + ' from deleted')
SET @Str = @Str + @PKField + ' = ' + @PKValue + ', '
SET @JoinStr = @JoinStr + 'i.' + @PKField + ' = d.' + @PKField + ' AND '
FETCH NEXT FROM c INTO @PKField
END
SET @JoinStr = LEFT(@JoinStr, LEN(@JoinStr) - 4)

CLOSE c
DEALLOCATE c

 

[druer]

The only thing that I can think of if you have to do it generically would be to select the INSERTED and DELETED into temp tables and then refer to them dynamically. I don't think you are going to like the performance of doing it this way.

Are you trying to keep the code generic simply to avoid the work of typing the code? You could run your loop for all tables and have PRINT statements print the text you want with the hardcoded names generated for you and then copy/paste that into your triggers to avoid massive overhead for every insert/update.

 

[stiej]

Cheers. Thanks for your replies.Im keeping the code generic so i can pop this trigger on other tables, and let it think for itself as to what the primary keys are in each table and fetch their vlaues out from the inserted/deleted tables and stitch them together into a string.

I might try getting the inserted or deleted table into a temp table like you suggested.

thanks agian.

 

[SQLSister]

You are aware that by making this generaizable to other tables you have decreased the efficiency of the trigger. You should avoid both dynamic SQL and cursors in triggers. This is generally a bad practice. Reuse of code which causes a trigger to run slower is not a good idea. You will hammer your server as you get lots of records. I mention this becasue I just started a job in a new place that also followed these practices and now that they have a 14 gig database, I have been hired to fix this mess because the application is frequently timing out.

I qwould drastically revisit how you intend to do the triggers. Saving a little development cost at the cost of a permanent production problem seems to be counterproductive.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief