Prev: exec of SP vs. exec of lines
Next: Linked Server Setup
From: AlanPorter on 23 Jul 2008 11:17
SQL Server 2005 SP1

After inserting into a table with an INSTEAD_OF trigger and an AFTER INSERT
trigger, the SCOPE_IDENTITY() function returns 0.
Is this per design ?

It looks like the function is using the scope of the INSTEAD_OF trigger,
whereas the identity column will be set by the insert operation itself.

Can someone think of a workaround ?


From: Alex Kuznetsov on 23 Jul 2008 11:28
On Jul 23, 10:17 am, AlanPorter <AlanPor...(a)discussions.microsoft.com>
wrote:
> SQL Server 2005 SP1
>
> After inserting into a table with an INSTEAD_OF trigger and an AFTER INSERT
> trigger, the SCOPE_IDENTITY() function returns 0.
> Is this per design ?
>
> It looks like the function is using the scope of the INSTEAD_OF trigger,
> whereas the identity column will be set by the insert operation itself.
>
> Can someone think of a workaround ?

Can you eliminate or INSTEAD OF TRIGGER? Also read about OUTPUT clause.
From: AlanPorter on 23 Jul 2008 11:44
Good idea. I'll use output, thanks.

"Alex Kuznetsov" wrote:

> On Jul 23, 10:17 am, AlanPorter <AlanPor...(a)discussions.microsoft.com>
> wrote:
> > SQL Server 2005 SP1
> >
> > After inserting into a table with an INSTEAD_OF trigger and an AFTER INSERT
> > trigger, the SCOPE_IDENTITY() function returns 0.
> > Is this per design ?
> >
> > It looks like the function is using the scope of the INSTEAD_OF trigger,
> > whereas the identity column will be set by the insert operation itself.
> >
> > Can someone think of a workaround ?
>
> Can you eliminate or INSTEAD OF TRIGGER? Also read about OUTPUT clause.
>
 | 
Pages: 1
Prev: exec of SP vs. exec of lines
Next: Linked Server Setup