July 1, 2004 at 1:25 pm
Hi,
I'm an intermediate user of SQL as a developer.
I've created an audit table in which I want to record which column has been updated and its old and new values. Among other functions, I'm using COLUMNS_UPDATED to determine which columns have been updated.
I create the SQL dynamically based on which column was updated. This works fine when I'm working with the data table. But, as soon as I use the SQL created with the 'inserted' table in it, it doesn't recognize the inserted table. The SQL: SET @strSQL = 'SELECT ' + @FieldName + ' FROM inserted WHERE WD_ID = ' + CAST(@RecordID as varchar(20)) + ' '
I use sp_executesql to execute the dynamic SQL. I thought that temp tables, including inserted and deleted were recognized. Am I mistaken?
There are 141 columns in this table, so I don't want to have to hard code a 'Case' statement. Not to mention that I'd like this to be flexible in case I change the table.
Let me know if I'm not clear. Thanks in advance for any help anyone can offer.
P
July 2, 2004 at 12:47 pm
The "inserted" and "deleted" pseudo-tables are visible only in the trigger, not in other batches called from the trigger. The solution would be to copy them in a "real" temporary table, before calling the dynamic SQL:
SELECT * INTO #TempInserted FROM inserted
SELECT * INTO #TempDeleted FROM deleted
SET @strSQL = 'SELECT ' + @FieldName + ' FROM #TempInserted WHERE ...'
...
Razvan
July 2, 2004 at 1:11 pm
Hey Razvan!
Thanks a lot for the help.
I had thought about that, but (and I forgot to mention this in my initial post) the table has a few text columns. As we all know, temporary tables can have text (and other) columns. So, it looks like I'm going to convert those columns to varchar.
P
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply