|
|
Posted by imani_technology_spam@yahoo.com on 11/27/18 11:58
Thank you for the response. Please bear in mind that the LabID is the
identifiable key; it is the PK for the table. Also, I don't understand
why LabID = @curyear + @curmax + i.newlabid instead of @curyear +
(@curmax + 1). Finally, could you please explain to me why the UNION
statements are necessary?
Again, thank you very much.
Erland Sommarskog wrote:
> 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]
|