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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Primary Key problem with insert trigger

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 1

Problem 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 INSERT
AS
BEGIN

SET NOCOUNT ON;


DECLARE @UnquieID as UniqueIdentifier
SELECT @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 and
SUBSTRING(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]

Go to Top of Page

micalo
Starting Member

2 Posts

Posted - 2012-11-17 : 00:45:39
THanks Khtan!!! :)
Go to Top of Page
   

- Advertisement -