|
|
Posted by imani_technology_spam@yahoo.com on 09/21/06 20:42
BLESS YOU!!! I'll look at this immediately.
Erland Sommarskog wrote:
> 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]
|