Board index » delphi » ADO, insert in trigger and identity

ADO, insert in trigger and identity

Hi,

my problem is that I have Table A and Table B. Table A insert trigger has
insert statement for Table B (audit trail). Both tables have idetity as ID.
The problem is that ADODataset get confused by the trigger an retrieve wrong
ID after post a new record. I use MS SQL 2000.

I know that I should use SCOPE IDENTITY instead of IDETINTY to get ID for
this case. But, HOW to tell ADO to do it? I can not believe that MS would
not fix this buggy behavior. Or is is a bug of AdoExpress? I use MDAC 2.6
and Delphi 5 with all patches.

I have foud some complex solutions when the identity has been saved at start
of the trigger and restored in the end. I think this is solution for MSSQL
7, not for MSSQL 2000?! Does exists some easier way?

Thanks

Jan Najvarek
ARTIN

 

Re:ADO, insert in trigger and identity


Quote
"Jan Najvarek" <najva...@mistral.cz> wrote in message

news:3d3ff7d4$1_2@dnews...

Quote
> my problem is that I have Table A and Table B. Table A insert trigger has
> insert statement for Table B (audit trail). Both tables have idetity as
ID.
> The problem is that ADODataset get confused by the trigger an retrieve
wrong
> ID after post a new record. I use MS SQL 2000.

> I know that I should use SCOPE IDENTITY instead of IDETINTY to get ID for
> this case. But, HOW to tell ADO to do it? I can not believe that MS would
> not fix this buggy behavior.

Guess what, Microsoft have not fixed this buggy behaviour. ADO still uses
@@Identity, rather than Scope_Identity. See
http://support.microsoft.com/default.aspx?scid=KB;EN-US;q233299

Andy Mackie.

Re:ADO, insert in trigger and identity


Can't you SELECT @MyVar=SCOPE_IDENTITY() (if in a trigger) and use the
@MyVar value when inserting into table B?

Bruce

Quote
"Jan Najvarek" <najva...@mistral.cz> wrote in message

news:3d3ff7d4$1_2@dnews...
Quote
> Hi,

> my problem is that I have Table A and Table B. Table A insert trigger has
> insert statement for Table B (audit trail). Both tables have idetity as
ID.
> The problem is that ADODataset get confused by the trigger an retrieve
wrong
> ID after post a new record. I use MS SQL 2000.

> I know that I should use SCOPE IDENTITY instead of IDETINTY to get ID for
> this case. But, HOW to tell ADO to do it? I can not believe that MS would
> not fix this buggy behavior. Or is is a bug of AdoExpress? I use MDAC 2.6
> and Delphi 5 with all patches.

> I have foud some complex solutions when the identity has been saved at
start
> of the trigger and restored in the end. I think this is solution for MSSQL
> 7, not for MSSQL 2000?! Does exists some easier way?

> Thanks

> Jan Najvarek
> ARTIN

Re:ADO, insert in trigger and identity


Quote
>I know that I should use SCOPE IDENTITY instead of IDETINTY to get ID for
>this case. But, HOW to tell ADO to do it? I can not believe that MS would
>not fix this buggy behavior. Or is is a bug of AdoExpress? I use MDAC 2.6
>and Delphi 5 with all patches.

If it is a bug its a bug in the Ole Db driver for SQL server.  Neither ADO or
ADOExpress know about @@Identity or SCOPE_IDENTITY()
--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Other Threads