EXEC and deleted from a trigger

[SauerC]

we have an update trigger that inserts a row into a database...we wanted to make that part be dynamic enough so the trigger could automatically handle changes to the table.

What I did was create a stored procedure that would return all the fields of the table, except the "change fields" we created. We then dynamically build the insert stable including values for the "change fields" and fire it off using EXEC ().

The problem is EXEC () is complaining that the table 'deleted' does not exist. This is really the last piece of the puzzle for us ... is there anyway to do what we're looking for ??

-Christian

example :

exec usp_GenUpdateSQL @TableName = 'Users', @ColNames = @UserColNames output
		
set @UpdSQL = 'insert into Users select ' + @UserColNames + ',''Y'' as change_ind, getdate() as change_date, 12 as change_who from deleted'

exec (@UpdSQL)

 

[tlbroadbent]

The deleted and inserted virtual tables only exist within the scope of the trigger. When you use Execute, the statement has its own scope. Thus inserted and deleted don't exist. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[SauerC]

So there is no way to run dynamic sql statement in the scope of the trigger?

-Christian

 

[tlbroadbent]

I didn't indicate you couldn't execute dynamic SQL in a trigger. You can execute dynamic SQL but the SQL can't reference the inserted and deleted tables. You could create a temp table, insert the rows of deleted into it and then reference it in the dynamic SQL. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[SauerC]

I ended up doing some similar...since the temp table has all the fields I need, but allows me to make changes

select *  into #MyDeleted from deleted

update #MyDeleted set change_ind = 'Y', change_date = getdate(), change_who = 12

insert into Users select * from #MyDeleted

Thanks for the help.

-Christian