|
micalo
Starting Member
Australia
2 Posts |
 Posted - 11/16/2012 : 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
|
|
Primary Key problem with insert trigger
