Board index » delphi » @@IDENTITY and Insert Triggers

@@IDENTITY and Insert Triggers

Hi,

Im using SQL 2000 and have an INSERT trigger which is affecting the value of
my @@IDENTITY value in the sp which does the insert. Now this is a well
known problem which I continually read was fixed back in SQL 7. Well it
still doesnt work, I know SQL 2k added the SCOPE_IDENTITY() function but
that returns an sql_variant which even when I cast as an int I get an error
when my Tadostoredproc component calls it. Was just wondering if anyone has
had this problem and what they did to resolve it.

Many thanks

Gary

 

Re:@@IDENTITY and Insert Triggers


"Gary" <garyk...@nospam.hotmail.com> ???Y/???Y ????? ???Y??:
news:3dc64eba@newsgroups.borland.com...
Quote
> Hi,

> Im using SQL 2000 and have an INSERT trigger which is affecting the value
of
> my @@IDENTITY value in the sp which does the insert. Now this is a well
> known problem which I continually read was fixed back in SQL 7. Well it
> still doesnt work, I know SQL 2k added the SCOPE_IDENTITY() function but
> that returns an sql_variant which even when I cast as an int I get an
error
> when my Tadostoredproc component calls it. Was just wondering if anyone
has
> had this problem and what they did to resolve it.

Hmm. May be I dont understand your problem, but this work fine for me:

CREATE PROCEDURE dbo.IS_USER_ENABLED
    @AMSISDN CHAR(15),
    @VID INTEGER OUTPUT,
    @VENABLED BIT OUTPUT,
    @VNOTE VARCHAR(250) = null OUTPUT
AS

  IF NOT EXISTS (SELECT * FROM IM_USER where MSISDN = @AMSISDN) BEGIN
    INSERT INTO IM_USER(MSISDN, ENABLED, NOTE)
    VALUES(@AMSISDN, 1, NULL)
    SELECT @VID = SCOPE_IDENTITY(), @VENABLED = 1, @VNOTE = NULL
------------------^^^^^^^ I read this value from SroredProc.Params
  END
  ELSE BEGIN
    select @VID = ID, @VENABLED = ENABLED, @VNOTE = NOTE
    from IM_USER
    where MSISDN = @AMSISDN
  END

Other Threads