January 16, 2017 at 5:06 pm
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.
January 16, 2017 at 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
January 16, 2017 at 9:10 pm
Sue_H - Monday, January 16, 2017 6:30 PMNot 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.
January 17, 2017 at 12:24 pm
Based on your sample data what is the end state you are looking for in the table test.<
January 18, 2017 at 3:32 pm
Lynn Pettis - Tuesday, January 17, 2017 12:24 PMBased 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
January 19, 2017 at 11:24 am
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