After insert trigger not working correctly with multiple inserts

  • This topic is related to https://www.sqlservercentral.com/Forums/Topic1835026-2799-1.aspx.

    I've changed the test code from that post due to slight changes in the table design in my environment.

    Here is test code that should work as is in your environment:

    DROP TRIGGER [dbo].[Update_BatchNum]
    GO

    ALTER TABLE [dbo].[test] DROP CONSTRAINT [DF_test_batch_id]
    GO

    DROP INDEX [IXNU_test_id] ON [dbo].[test]
    GO

    DROP TABLE [dbo].[test]
    GO

    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[test](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [project_id] [smallint] NOT NULL,
    [service_id] [tinyint] NOT NULL,
    [batch_id] [int] NOT NULL,
    CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
    (
    [project_id] ASC,
    [service_id] ASC,
    [batch_id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    CREATE NONCLUSTERED INDEX [IXNU_test_id] ON [dbo].[test]
    (
    [id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[test] ADD CONSTRAINT [DF_test_batch_id] DEFAULT ((-1)) FOR [batch_id]
    GO

    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TRIGGER [dbo].[Update_BatchNum]
    ON [dbo].[test]
    AFTER INSERT
    AS
    BEGIN
    WITH cteMaxBatchNum AS (
    SELECT
    b.project_id,
    b.service_id,
    MaxBatchNum = MAX(CASE WHEN b.batch_id < 0 THEN 0 ELSE b.batch_id END)
    FROM
    TEST AS b
    INNER JOIN
    INSERTED AS i
    ON
    b.project_id = i.project_id AND b.service_id = i.service_id
    GROUP BY
    b.project_id, b.service_id
      )
      , cteNewBatchNum AS (
    SELECT
    i.project_id,
    i.service_id,
        NewBatchNum = m.MaxBatchNum + ROW_NUMBER() OVER (
    PARTITION BY
    i.project_id, i.service_id
    ORDER BY
    i.project_id, i.service_id
    )
    FROM
    TEST AS b
    INNER JOIN
    INSERTED AS i
    ON
    b.project_id = i.project_id AND b.service_id = i.service_id
    INNER JOIN
    cteMaxBatchNum AS m
    ON
    b.project_id = i.project_id AND b.service_id = m.service_id
      )
      UPDATE b
      SET
    batch_id = n.NewBatchNum
      FROM
    TEST AS b
      INNER JOIN
    cteNewBatchNum AS n
    ON
    b.project_id = n.project_id AND b.service_id = n.service_id
      WHERE
    b.batch_id = -1
    END

    GO

    To test:

    INSERT INTO [dbo].[test]
    (
    [project_id]
    ,[service_id]
    )
    VALUES
    (1,1)

    SELECT * FROM test

    Execute multiple times.  So far so good.  But this fails:

    INSERT INTO [dbo].[test]
    (
    [project_id]
    ,[service_id]
    )
    VALUES
    (1,1),(1,1),(1,1)

    SELECT * FROM test

    I removed the PK, and see that the trigger isn't doing the right thing for multiple inserts.  So I must have edited the trigger from the previous post incorrectly.

    Thanks for the help.

  • Not sure what you mean by not working correctly but if you change that insert to be:
    VALUES
    (1,1),(1,1), (1,1), (1,1), (1,1)

    Then the batch number is the same for five rows. If the repeating batch number is the issue then that's how they work. Triggers fire once per batch.

    Sue

  • Sue_H - Monday, January 16, 2017 6:30 PM

    Not sure what you mean by not working correctly but if you change that insert to be:
    VALUES
    (1,1),(1,1), (1,1), (1,1), (1,1)

    Then the batch number is the same for five rows. If the repeating batch number is the issue then that's how they work. Triggers fire once per batch.

    Sue

    Hi Sue,

    From https://www.sqlservercentral.com/Forums/Topic1835026-2799-1.aspx (replace "ServiceNum" from that post with "batch_id" here):

    1) I want to keep ServiceNum batch_id NOT NULL so the user cannot set the value to NULL.
    2) I want to allow insert of null ServiceNum batch_id, and have the trigger set the correct value. I thought DEFAULT=-1 would allow that.
    3) The trigger isn't working. The correct values for ServiceNum batch_id should be:  (max+1 grouped within project_id, service_id)
    4) It should work with multiple inserts

    Yes, the repeating batch number is the issue.  project_id + service_id + batch_id should be unique.

    For #4), I thought the ROW_NUMBER() OVER PARTITION BY would handle this even for multiple inserts, since ROW_NUMBER() is added to the initially derived MaxBatchNum.

  • Based on your sample data what is the end state you are looking for in the table test.<

  • Lynn Pettis - Tuesday, January 17, 2017 12:24 PM

    Based on your sample data what is the end state you are looking for in the table test.<

    The end state should be the same whether doing a single insert or a multiple insert.

    So, these results should be identical:

    INSERT INTO [dbo].[test] ([project_id],[service_id]) VALUES (1,1)
    INSERT INTO [dbo].[test] ([project_id],[service_id]) VALUES (1,1)
    INSERT INTO [dbo].[test] ([project_id],[service_id]) VALUES (1,1)
    INSERT INTO [dbo].[test] ([project_id],[service_id]) VALUES (2,1)
    INSERT INTO [dbo].[test] ([project_id],[service_id]) VALUES (2,1)
    INSERT INTO [dbo].[test] ([project_id],[service_id]) VALUES (1,1)

    vs.

    INSERT INTO [dbo].[test] ([project_id],[service_id]) VALUES (1,1),(1,1),(1,1),(2,1),(2,1),(1,1)

    Those results should be:

    1 1 1
    1 1 2
    1 1 3
    2 1 1
    2 1 2
    1 1 4

    And analogous results from:

    INSERT INTO dbo.test
    (project_id,service_id)
    SELECT project_id,service_id
    FROM some.table

  • Try the following for your trigger:


    create trigger dbo.UpdateBatchNum on dbo.test after insert
    as
    begin
    with BaseData as (
    select
      project_id,
      service_id,
      BaseBatchId = MAX(case when batch_id = -1 then 0 else batch_id end)
    from
      dbo.test
    group by
      project_id,
      service_id
    ), UpdateData as (
    select
     t.id,
     t.project_id,
     t.service_id,
     BatchId = bd.BaseBatchId + ROW_NUMBER() over (partition by t.project_id, t.service_id order by t.id)
    from
      inserted t
      inner join BaseData bd
        on t.project_id = bd.project_id and t.service_id = bd.service_id
    )
    update t set
      batch_id = ud.BatchId
    from
      dbo.test t
      inner join UpdateData ud
        on t.id = ud.id
    end
    <

Viewing 6 posts - 1 through 5 (of 5 total)

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