January 23, 2007 at 9:08 am
I am trying to attach a control number to each record updated or inserted into a table (i.e. when a record is inserted or updated assign that record the highest current value of the control record + 1). So everytime I update a record I want that record to get a new, higher control number (Max(controlNumber) +1). Anybody know how I can automate this so it automatically does it on any updates or inserts?
January 23, 2007 at 10:03 am
A trigger should work although it would probably be more efficient to code the logic when you INSERT or UPDATE. If you want to use a trigger, something like the following should work:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TABLE dbo.Test
(
PK int NOT NULL
CONSTRAINT PK_Test PRIMARY KEY
,TestCol varchar(20) COLLATE DATABASE_DEFAULT NOT NULL
,ControlNumber int NOT NULL
CONSTRAINT UQ_Test_ControlNumber UNIQUE
)
GO
CREATE TRIGGER Test_ControlNumber
ON dbo.Test
AFTER INSERT, UPDATE
AS
DECLARE @s-2 TABLE
(
PK int NOT NULL PRIMARY KEY
,Seq int IDENTITY NOT NULL
)
INSERT INTO @s-2(PK)
SELECT PK
FROM inserted
UPDATE T
SET ControlNumber = M.ControlNumber + S.Seq
FROM dbo.Test T
JOIN @s-2 S ON T.PK = S.PK
CROSS JOIN (
SELECT ISNULL(MAX(ControlNumber), 1) AS ControlNumber
FROM dbo.Test T1 WITH (UPDLOCK) ) M
GO
If you are just interested in whether a row has changed then make ControlNumber a timestamp column.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply