Results 1 to 4 of 4
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    No Identity - Trigger (Tranact SQL 2000)

    I have the following code that is to be used in a trigger on a table called incident
    <pre>SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO

    ALTER TRIGGER trigInsert104
    ON incident
    AFTER INSERT
    AS
    BEGIN
    DECLARE @ProductID varchar(20), @count integer



    SELECT @ProductID = vchProductID
    FROM incident
    WHERE iincidentid= (select max(iincidentid) from incident)





    SELECT @count = count(*)
    FROM csclosestatus
    WHERE chProductnumber = @ProductID




    if @count>0
    BEGIN
    UPDATE incident
    SET istatusid='104'
    WHERE iincidentid= (select max(iincidentid) from incident)
    END
    END

    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO
    </pre>


    My issue is with the 2 lines

    WHERE iincidentid= (select max(iincidentid) from incident)

    The issue is that iincidentid has no identity and therefore it is not incremental and as an example the inderted iicidentid was 2515 but the max(iincidentid) was 65716 and therefore the wrong record is being updated

    Due to the lack of identity i cannot use scope_identity.

    Any ideas how i can get the current record iincident value from the after insert?
    Jerry

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: No Identity - Trigger (Tranact SQL 2000)

    You can try @@IDENTITY or else something here might offer some suggestions you could use.
    Charlotte

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: No Identity - Trigger (Tranact SQL 2000)

    Thanks Charlotte that was my first attempt but as it has no increment I cannot use it as it looks at the increment

    ident_current <img src=/S/nope.gif border=0 alt=nope width=15 height=15>
    scope_identity <img src=/S/nope.gif border=0 alt=nope width=15 height=15>

    I have a feeling that I will have to create a temp table or do an insert to view the current record ID on the trigger. This is a SQL/XML application that is not bespoke so I am limited to this I think...or am I?
    Jerry

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: No Identity - Trigger (Tranact SQL 2000)

    Thanks to an inspirational gentleman at the office we have solved the problem by using the from inserted clause as follows

    <pre>SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO


    ALTER TRIGGER trigInsert104
    ON incident
    AFTER INSERT
    AS
    BEGIN
    DECLARE @ProductID varchar(20), @count integer , @IncidentID integer


    SELECT @ProductID = vchProductID, @IncidentID = iincidentid
    FROM inserted






    SELECT @count = count(*)
    FROM csclosestatus
    WHERE chProductnumber = @ProductID




    if @count>0
    BEGIN
    UPDATE incident
    SET istatusid='104'
    WHERE iincidentid= @IncidentID
    END
    END


    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    </pre>

    Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •