Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2003
    Posts
    41

    Unanswered: referencing inserted and deleted tables with sp_executeSql

    Hi everyone. Thanks in advance to anyone who might be able to shed some light on this situation.

    I have a trigger in which the following SQL code exists.

    SET @tempInserted = N'SET @dummy = (SELECT '+@cftColumnName+' FROM INSERTED)'
    EXEC sp_executeSQL @tempInserted, N'@dummy varchar(255) output', @dummy=@tempAddress output

    When the trigger executes I receive the following error message....

    Server: Msg 208, Level 16, State 1, Line 1
    Invalid object name 'INSERTED'.

    My question is, is it possible to in some way reference the INSERTED and DELETED tables using sp_executeSQL?

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    I was able to replicate your problem:
    Code:
    -- Set Option               Value      
    -- ------------------------ ---------- 
    -- textsize                 64512
    -- language                 us_english
    -- dateformat               mdy
    -- datefirst                7
    -- arithabort               SET
    -- nocount                  SET
    -- remote_proc_transactions SET
    -- ansi_null_dflt_on        SET
    -- ansi_warnings            SET
    -- ansi_padding             SET
    -- ansi_nulls               SET
    -- concat_null_yields_null  SET
    
    create table #Tmp(f1 int, f2 char(1))
    go
    create trigger TmpTrigger on Tmp
    FOR DELETE, INSERT, UPDATE 
    AS 
    BEGIN
    	declare @tempInserted  nvarchar(100)
            , @cftColumnName nvarchar(100)
            , @tempAddress   nvarchar(100)
      set @cftColumnName = 'f2'
      SET @tempInserted = N'SELECT @dummy = ' + @cftColumnName + ' FROM inserted'
      SET @tempInserted = N'select * From #Tmp'
      EXEC sp_executeSQL @tempInserted, N'@dummy varchar(255) output', @dummy=@tempAddress output
      select @tempAddress
    END
    go
    insert into #Tmp values(2,'B')
    insert into Tmp values(1,'A')
    select * From Tmp
    go
    drop table #Tmp
    drop table Tmp
    go
    The only thing I can figure out is that since inserted and deleted are special temp tables they are not available to the new process created during the execution of sp_executeSQL.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Feb 2003
    Posts
    41
    Thanks Paul. I ended up taking a different approach that essentially enabled me to achieve what I was attempting to do. Instead of directly referencing the INSERTED and DELETED tables I first create another set of temp tables to which I copy all the records from INSERTED and DELETED. I can then make a reference to these temporary tables during the execution of sp_executeSQL.

    Not perfect but it works =).

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    A workable solution is better than nothing working! You can always go back and change your code once everything runs end to end.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •