Using variables with inserted and deleted tables.

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

Transact-SQL (2000)

Using variables with inserted and deleted tables.

Author

Topic

hmartyb
Starting Member

5 Posts

Posted - 2002-07-18 : 19:44:28

I'm an experienced vb programmer that is just starting with Transact-SQL, so bear with me if I'm asking a dumb question here

.

I'm making a trigger which inserts info into an audit table. On insert or update, I basically loop through all columns on my table, check them to see if they're updated, and, if they are, I make note of it in the audit table (username, date/time, ect.)

Here's where it gets tricky. I have the column name that was updated stored in a variable. I want to go to the inserted and deleted tables to get before and after values for that column. Here's an example (I know it's not this simple, but it will illustrate my question):

DECLARE @MyColumnName char(20)

INSERT INTO MyTable
(OldValue,
NewValue)
SELECT
del.@MyColumnName,
ins.@MyColumnName
FROM inserted ins, deleted del

I've been trying to work this out for quite a while now. I think the answer has something to do with dynamic SQL, but I just keep missing it.

Thanks in advance,

Marty

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-07-18 : 20:54:40

SQL is assuming your variable to be an expression you are trying to select from the tables. e.g. if your variable is = 'LastName', you will get the value 'LastName' for as many rows as there are in the inserted and deleted tables. Illustration: Open Query Analyzer, and type
DECLARE @MyCol varchar(20)
SET @MyCol = 'Lastname'
SELECT @Mycol FROM YourTable

Dynamic SQL will work like this:
Declare a varchar variable large enough to hold your insert statement, in addition to your column variable, and concatenate them ('INSERT INTO MyTable(Oldvalue,NewValue)SELECT del.' + @MyColumn + ',ins.' + @MyColumn + ' FROM Inserted ins, Deleted del') and then do EXEC(@InsertVar)

Sarah Berger MCSD

hmartyb
Starting Member

5 Posts

Posted - 2002-07-19 : 12:49:51

Thanks Sarah, but I have tried this:

SET @InsertVar = 'INSERT INTO MyTable(Oldvalue,NewValue)SELECT del.' + @MyColumn + ',ins.' + @MyColumn + ' FROM Inserted ins, Deleted del'

EXEC(@InsertVar)

The problem is that I get an error calling Inserted and Deleted invalid objects. They must be out of scope inside the EXEC. I have tried referencing them like MyServer.MyDatabase.dbo.inserted, which didn't help. Any other suggestions? Is Transact-SQL too limited to perform a task like this?

Thanks again,

Marty

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-19 : 13:02:51

create trigger name_thy_trigger
on name_thy_table
for update
as
declare @sql varchar(500)
declare @mycolumnname
set @mycolumnname = --need a way to set the column name. whateva ya want
set @sql = 'insert into mytable(oldvalue,newvalue) select del.'+@mycolumnname+',ins.'+@mycolumnName+' from inserted ins, deleted del'
exec(@sql)

The reason they are invalid objects is only triggers may access these tables.

edit : man, spelling errors all over the place
-----------------------
Take my advice, I dare ya

Edited by - M.E. on 07/19/2002 13:11:00

hmartyb
Starting Member

5 Posts

Posted - 2002-07-19 : 13:25:15

"The reason they are invalid objects is only triggers may access these tables."

I kinda figured as much. Is there any way to dynamically reference column names in the inserted/deleted tables?

hmartyb
Starting Member

5 Posts

Posted - 2002-07-19 : 13:31:52

Oops, sorry M.E. I didn't completely get the gist of your reply

.
To clarify, I'm getting the invalid object error in the trigger! I think the EXEC changes the scope of reference to the inserted/deleted tables.

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-19 : 13:53:28

hmm.. that'd be new to me. Quick run around using temp table I guess

select * into #mytabledeleted from deleted
select * into #mytableinserted from inserted

then use
set @sql = 'insert into mytable(oldvalue,newvalue) select del.'+@mycolumnname+',ins.'+@mycolumnName+' from #mytabledeleted del, #mytableinserted ins'

What Database are you using?

-----------------------
Take my advice, I dare ya

hmartyb
Starting Member

5 Posts

Posted - 2002-07-19 : 14:30:13

Thanks, temp tables did the trick! I'm using MS 2000 Standard.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources