November 15, 2016 at 2:53 pm
I need to write an after insert trigger to increment a column to max+1 based on a grouping column.
Here's what I tried:
DROP TABLE [dbo].[test]
GO
CREATE TABLE [dbo].[test]
(
[ServiceID] [int] IDENTITY(1,1) NOT NULL,
[ProjectID] [int] NOT NULL,
[ServiceNum] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[test] ADD DEFAULT (-1) FOR [ServiceNum]
GO
CREATE TRIGGER [dbo].[Update_ServiceNum]
ON [dbo].[Test]
AFTER INSERT
AS
BEGIN
UPDATE dbo.test
SET ServiceNum = (SELECT MAX(s.ServiceNum) + 1
FROM oper.Service s
INNER JOIN INSERTED i ON s.ProjectID = i.ProjectID
WHERE s.ServiceNum = -1)
END
GO
INSERT INTO dbo.test (ProjectID)
VALUES (1),(2),(1),(3),(1),(2)
GO
SELECT * FROM dbo.test
Problems:
1) I want to keep ServiceNum NOT NULL so the user cannot set the value to NULL.
2) I want to allow insert of null ServiceNum, 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 should be:
Row 1: 1
Row 2: 1
Row 3: 2
Row 4: 1
Row 5: 3
Row 6: 2
4) It should work with multiple inserts
Thanks for any advice you can give...
November 16, 2016 at 4:47 am
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 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply