Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
17689 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  
 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.02 seconds. Powered By: Snitz Forums 2000