Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
micalo
Starting Member
2 Posts |
Posted - 2012-11-16 : 00:30:18
|
Hi,Trying to write a trigger on a table that has a integer primary key but is not a identity. What I have to do is grab the max(id) and add 1Problem is when I do this and there is more than 1 record in the inserted table I get a 'violation of primary key constraint' error because its using the same id.I have included the trigger below ( the problem part is SELECT MAX(ExternalFileLinks.ExternalFileLinkID)+1 FROM ExternalFileLinks )CREATE TRIGGER [dbo].[InsertDrawingRevFilesToTransmittalItems] ON [dbo].[TransmittalItems] AFTER INSERTAS BEGINSET NOCOUNT ON;DECLARE @UnquieID as UniqueIdentifierSELECT @UnquieID = (SELECT Top 1 Transmittals.GUID FROM Transmittals WHERE TransmittalID in (SELECT TransmittalID from inserted))INSERT INTO ExternalFileLinks (ExternalFileLinkID,LinkedTableID,LinkedGUID,LoggedBy,LoggedDate,Description,Notes,Path,FileName,Topics,FileVersionID,ThirdPartyServer,ProjectID)SELECT (SELECT MAX(ExternalFileLinks.ExternalFileLinkID)+1 FROM ExternalFileLinks), 122,@UnquieID ,LoggedBy,GETDATE(),Description,Notes,Path,FileName,Topics,FileVersionID,ThirdPartyServer,ProjectID FROM ExternalFileLinks WHERE LinkedGUID in (SELECT DrawingsSpecs.GUID FROM DrawingsSpecs LEFT OUTER JOIN Inserted on DrawingsSpecs.ProjectID = Inserted.ProjectID and SUBSTRING(Reference,8,CHARINDEX ('Revision',Reference)-9)=DrawingsSpecs.Number andSUBSTRING(Reference,(CHARINDEX ('Revision',Reference)+9),CHARINDEX (' ',Reference,(CHARINDEX ('Revision',Reference)+10))-CHARINDEX ('Revision',Reference)-9)=DrawingsSpecs.RevisionNumber WHERE Inserted.Item ='Drawings and Specifications' and CHARINDEX ('Revision',Reference)>0 and CHARINDEX ('Sheet',Reference)>0)END |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-11-16 : 00:37:41
|
use row_number() to return a continuous running no[new ID] = < max ID > + row_number() over (order by somecol) KH[spoiler]Time is always against us[/spoiler] |
|
|
micalo
Starting Member
2 Posts |
Posted - 2012-11-17 : 00:45:39
|
THanks Khtan!!! :) |
|
|
|
|
|
|
|