|
|
Posted by imani_technology_spam@yahoo.com on 11/28/72 11:59
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.
imani_technology_spam@yahoo.com wrote:
> 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]
|