Exec cannot see Inserted?

8 Sep 2006 3:40 PM

Michel Racicot

If I do the following withing a trigger:

declare @SQLText NVarChar(4000)
set @SQLText = N'select *, cast(''I'' as varchar(1)) as FlagUID into ' +
@TmpTableName + ' from Inserted'
exec (@SQLText)

SQL Server tells me that "Inserted" is not recognized anymore...

This is the error message I got when debugging:
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
'Inserted'.

How can I solve this problem?

8 Sep 2006 4:12 PM

SQL Menace

Out of scope, try this kludge

select * into #Inserted from Inserted

declare @SQLText NVarChar(4000)
set @SQLText = N'select *, cast(''I'' as varchar(1)) as FlagUID into
' +
@TmpTableName + ' from #Inserted'
exec (@SQLText)

Untested of course ;-)

Denis the SQL Menace
http://sqlservercode.blogspot.com/

Michel Racicot wrote:
Show quote

> If I do the following withing a trigger:
>
>   declare @SQLText NVarChar(4000)
>   set @SQLText = N'select *, cast(''I'' as varchar(1)) as FlagUID into ' +
> @TmpTableName + ' from Inserted'
>   exec (@SQLText)
>
> SQL Server tells me that "Inserted" is not recognized anymore...
>
> This is the error message I got when debugging:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
> 'Inserted'.
>
> How can I solve this problem?

8 Sep 2006 4:51 PM

Arnie Rowland

Michael,

I'm very curious about what you are doing that you have to use dynamic SQL
in a Trigger. I've never done that before and I'm wondering what
functionality I'm missing.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"Michel Racicot" <mraci***@hotmail.com> wrote in message
news:O6RdPy10GHA.4796@TK2MSFTNGP03.phx.gbl...
> If I do the following withing a trigger:
>
> declare @SQLText NVarChar(4000)
> set @SQLText = N'select *, cast(''I'' as varchar(1)) as FlagUID into ' +
> @TmpTableName + ' from Inserted'
> exec (@SQLText)
>
> SQL Server tells me that "Inserted" is not recognized anymore...
>
> This is the error message I got when debugging:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
> 'Inserted'.
>
> How can I solve this problem?
>

8 Sep 2006 6:19 PM

Michel Racicot

Call me crazy but I'm simulating a commit/rollback transaction similar to
those of Interbase where there are no page locks and no table locks.

The basic idea is that whenever a new row is about to be inserted, deleted
or modified, we keep a copy of the old row (or the key of the new inserted
row) in a temporary table with the same structure as the modified table.

All rows inserted in the db are auto-commited to avoid locking.

Then, we got a stored procedure that will revert to the old datas whenever
we like...

Show quote

"Arnie Rowland" <ar***@1568.com> wrote in message
news:OGfsQc20GHA.324@TK2MSFTNGP05.phx.gbl...
> Michael,
>
> I'm very curious about what you are doing that you have to use dynamic SQL
> in a Trigger. I've never done that before and I'm wondering what
> functionality I'm missing.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
>
> "Michel Racicot" <mraci***@hotmail.com> wrote in message
> news:O6RdPy10GHA.4796@TK2MSFTNGP03.phx.gbl...
>> If I do the following withing a trigger:
>>
>> declare @SQLText NVarChar(4000)
>> set @SQLText = N'select *, cast(''I'' as varchar(1)) as FlagUID into ' +
>> @TmpTableName + ' from Inserted'
>> exec (@SQLText)
>>
>> SQL Server tells me that "Inserted" is not recognized anymore...
>>
>> This is the error message I got when debugging:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
>> 'Inserted'.
>>
>> How can I solve this problem?
>>
>
>

8 Sep 2006 10:38 PM

Erland Sommarskog

Michel Racicot (mraci***@hotmail.com) writes:

> If I do the following withing a trigger:
>
>   declare @SQLText NVarChar(4000)
>   set @SQLText = N'select *, cast(''I'' as varchar(1)) as FlagUID into ' +
> @TmpTableName + ' from Inserted'
>   exec (@SQLText)
>
> SQL Server tells me that "Inserted" is not recognized anymore...

Yes, EXEC() opens a new scope, and inserted/deleted is avilable only
directly in the trigger.

The simples workaround as Dennis suggest to use a temp table. I would
however adjust his suggestion. Instead of calling the temp table #inserted,
call the table #tablename_inserted or somesuch. That is, the name should
be unique for the trigger. This is in case you update another table
from trigger and this table has a trigger with the same construction.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx