|
|
Posted by Erland Sommarskog on 11/22/53 11:58
imani_technology_spam@yahoo.com (imani_technology_spam@yahoo.com) writes:
> The table in the database has a field called LabID. That field is an
> integer and consists of the year plus a counter. For example, the
> first record of 2006 would be "20060001," the second record of 2006
> would be "20060002" and so on. I'm trying to create an Insert trigger
> that can generate the ID value when a new record is inserted, but I'm
> not quite sure how to implement that trigger. Can anyone help?
The script below demonstrates. Note that it presumes that the table
already has an identifyable key, else there is no possibility to handle
multi-row inserts.
The solution presumes SQL 2005.
CREATE TABLE nisse (a int NOT NULL PRIMARY KEY,
LabID int NULL)
CREATE INDEX LabID_ix ON nisse(LabID)
go
CREATE TRIGGER nisse_tri ON nisse FOR INSERT AS
DECLARE @curmax int,
@curyear int,
@rowc int
SELECT @rowc = @@rowcount
IF @rowc = 0
RETURN
SELECT @curyear = year(getdate()) * 10000
SELECT @curmax = coalesce(MAX(LabID) - @curyear, 0)
FROM nisse (HOLDLOCK)
WHERE LabID BETWEEN @curyear + 1 AND @curyear + 9999
IF @curmax + @rowc > 10000
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('%d rows have already been inserted this year, so you cannot
insert %d rows now. Please wait until next year',
16, 1, @curmax, @rowc)
RETURN
END
UPDATE nisse
SET LabID = @curyear + @curmax + i.newlabid
FROM nisse n
JOIN (SELECT a, newlabid = row_number() over (ORDER BY a)
FROM inserted) AS i ON n.a = i.a
go
INSERT nisse (a)
SELECT 8
UNION ALL SELECT 188
UNION ALL SELECT 234
go
select * from nisse
INSERT nisse (a)
SELECT 18
UNION ALL SELECT 5188
UNION ALL SELECT 9234
go
select * from nisse
drop table nisse
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|