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
