After insert trigger not working correctly with multiple inserts

Question

After insert trigger not working correctly with multiple inserts

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

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

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

Scott In Sydney Hall of Fame Points: 3331 More actions · Answer 2

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

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

Scott In Sydney Hall of Fame Points: 3331 More actions · Answer 4

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

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
<