|
|
Posted by Erland Sommarskog on 09/19/06 21:17
imani_technology_spam@yahoo.com (imani_technology_spam@yahoo.com) writes:
> I'm using SQL Server 2005. By the way, the front-end app is an Access
> Data Project.
Ok, here is a revised and tested version using an INSTEAD OF trigger:
CREATE TABLE nisse (LabID int NOT NULL PRIMARY KEY,
somemoredata int NULL)
go
CREATE TRIGGER nisse_tri ON nisse INSTEAD OF 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
INSERT nisse(LabID, somemoredata)
SELECT @curyear + @curmax + row_number() OVER (ORDER BY newid()),
somemoredata
FROM inserted
go
INSERT nisse (somemoredata)
SELECT 8
UNION ALL SELECT 188
UNION ALL SELECT 234
go
select * from nisse
INSERT nisse (somemoredata)
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]
|