Create after insert trigger to increment column based on a grouping variable

  • This trigger should give you the desired results.

    CREATE TRIGGER [dbo].[Update_ServiceNum]

    ON [dbo].[Test]

    AFTER INSERT

    AS

    BEGIN

    WITH cteMaxServiceNum AS (

    SELECT s.ProjectID, MaxServiceNum = MAX(CASE WHEN s.ServiceNum < 0 THEN 0 ELSE s.ServiceNum END)

    FROM dbo.Test AS s

    INNER JOIN INSERTED AS i ON s.ProjectID = i.ProjectID

    GROUP BY s.ProjectID

    )

    , cteNewServiceNum AS (

    SELECT i.ServiceID,

    NewServiceNum = m.MaxServiceNum + ROW_NUMBER() OVER (PARTITION BY i.ProjectID ORDER BY i.ServiceID)

    FROM dbo.Test AS s

    INNER JOIN INSERTED AS i ON s.ServiceID = i.ServiceID

    INNER JOIN cteMaxServiceNum AS m ON s.ProjectID = m.ProjectID

    )

    UPDATE s

    SET ServiceNum = n.NewServiceNum

    FROM dbo.Test AS s

    INNER JOIN cteNewServiceNum AS n ON s.ServiceID = n.ServiceID

    WHERE s.ServiceNum = -1

    END

Viewing post 1 (of 2 total)

You must be logged in to reply to this topic. Login to reply