Working with triggers and sp_executesql

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Working with triggers and sp_executesql

Author

Topic

squisher
Starting Member

6 Posts

Posted - 2001-12-11 : 08:42:56

Hi group, I'm trying to implement a dynamic audit trail that will work on
any table.

I have had some problems using the sp_executesql in the trigger becuse it
does not find the'Deleted' and 'Inserted' temp tables, running outside the
trigger process I think.

First I wrote this to get number of columns in a table...

SELECT @TABLENAME = 'myTable'
SELECT @TABLENAMENR = (SELECT OBJECT_ID(@TABLENAME))
SELECT @COUNTER = (SELECT count(COL_NAME(@TABLENAMENR, ORDINAL_POSITION))

Then I iterated @COUNTER to audit each column if there where any change...

SELECT @COLUMNNAME = (SELECT distinct COL_NAME(@TABLENAMENR, @COUNTER)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLENAME)

And here below on the 'SELECT @INSERTVALUE' row the trigger cant find the 'Inserted' table...

SELECT @SQLSTATEMENT = ('SELECT ' + @COLUMNNAME + ' FROM '+ 'INSERTED')
SELECT @INSERTVALUE = (EXECUTE sp_executesql @SQLSTATEMENT)
EXECUTE @INSERTVALUE = sp_executesql @SQLSTATEMENT

This is not the whole trigger just a few rows to let you get the idéa about
my problem with building a dynamic trigger, if I can find a way of letting
sp_executesql find my temp tables my trigger will work.

TIA
-Stefan-

fisherman_jake
Slave to the Almighty Yak

159 Posts

Posted - 2001-12-11 : 09:11:14

G'day, more like good evening... I may be wrong, but I know that I will be corrected by the guys. All temp tables from my memory are created either on tempdb or master, so if you know the name of your temp table then just access them directly master..#temptable or tempdb..#temptable... That may be purely speculation on my part, but I think it may be a good guess, I'm only saying this coz I may have tried it in the past and don't quite remember the outcome, plus I am at home so it's a bit hard for me to try to check it for you, so I'll leave it for you to investigate.. If your topic is still here when I get to work in the morning then I'll verify it for ya.. Good luck..

==================================================
World War III is imminent, you know what that means... No Bag limits!!!
Master Fisherman

nr
SQLTeam MVY

12543 Posts

Posted - 2001-12-11 : 09:16:40

Nope that won't work.
temp tables are in tempdb and prefixing with the database won't help.

In this case though the attempt is to access the inserted/deleted tables which unfortunatley aren't available in the sp_executesql batch.

You can select * into #inserted from inserted then use #inserted in the dsql though.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

squisher
Starting Member

6 Posts

Posted - 2001-12-11 : 09:55:58

Thank you for your answers, I have thought about storing in a temp table like 'select * into #inserted from inserted'.

But then I have to create the temp table first...
CREATE TABLE #Inserted ('All the datatype defined columns from Inserted')

I cant find a smooth way to dynamicly define the temp table so that it becomes a copy of the current 'Inserted' table(I cant hard code the values as I dont know what the 'Inserted table will hold).

If anyone know a good method to create the temp table based upon the 'Inserted' table, I could use your suggestion 'Nr'.

TIA
-Stefan-

squisher
Starting Member

6 Posts

Posted - 2001-12-11 : 13:32:29

*Bump*

fisherman_jake
Slave to the Almighty Yak

159 Posts

Posted - 2001-12-12 : 01:51:05

Thanks for the correction nr, squisher, the Select * Into #Inserted will create the table for you.. No need to use CREATE TABLE blah blah... nr's advise will sure help you.. He's a SQL legend!!

==================================================
World War III is imminent, you know what that means... No Bag limits!!!
Master Fisherman

squisher
Starting Member

6 Posts

Posted - 2001-12-12 : 02:06:55

Thank you so much both of you, I didnt know it was made on the fly.
This will make things so much easier :)

I'm of to run some tests.

-Stefan-

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources