Dynamic SQL in Trigger, returns: Invalid object name inserted

• Peter Cunicelli

  Old Hand

  Points: 314

  More actions

  July 1, 2004 at 1:25 pm

  #85839

  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

   

• Razvan Socol

  SSCarpal Tunnel

  Points: 4962

  More actions

  July 2, 2004 at 12:47 pm

  #513215

  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

• Peter Cunicelli

  Old Hand

  Points: 314

  More actions

  July 2, 2004 at 1:11 pm

  #513218

  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