SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Primary Key problem with insert trigger
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

micalo
Starting Member

Australia
2 Posts

Posted - 11/16/2012 :  00:30:18  Show Profile  Reply with Quote
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)

Singapore
17645 Posts

Posted - 11/16/2012 :  00:37:41  Show Profile  Reply with Quote
use row_number() to return a continuous running no

[new ID] = < max ID > + row_number() over (order by somecol)



KH
Time is always against us

Go to Top of Page

micalo
Starting Member

Australia
2 Posts

Posted - 11/17/2012 :  00:45:39  Show Profile  Reply with Quote
THanks Khtan!!! :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000