Insert Trigger Doesn't seem to work with Insert/Select

[marksg]

I am using a trigger to create a sequence number, the secondary field on a primary key. The trigger works when I manually input records into the table but I get the following error when I use an INSERT/SELECT : Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_CRIntrvsC'. Cannot insert duplicate key in object 'CRIntrvsC'.
The statement has been terminated. How can I fix this?
Thanks

Trigger Code:
CREATE TRIGGER [IntrvSeqNbr] ON [dbo].[CRIntrvsC]
FOR INSERT
AS

IF EXISTS (SELECT cr_cse_id FROM Inserted WHERE intrvention_seq_nbr=0)
BEGIN
DECLARE @seq INT
SELECT @seq=MAX(CRI.intrvention_seq_nbr)+1
FROM CRIntrvsC CRI
INNER JOIN Inserted I
ON CRI.cr_cse_id = I.cr_cse_id

UPDATE CRIntrvsC
SET intrvention_seq_nbr = @seq
FROM CRIntrvsC CRI
INNER JOIN Inserted I
ON CRI.cr_cse_id = I.cr_cse_id
AND CRI.intrvention_seq_nbr=I.intrvention_seq_nbr
END

Insert Code:
INSERT INTO CrIntrvsC (cr_cse_id
, intrvention_seq_nbr
, intrvention_src_cd
, intrvention_id
, actv_rsn_cd
, actv_typ_cd
, otcome_typ_cd
, PARS_intrvention_catgy_cd
, PARS_intrvention_cd
, intrvention_cnt
)

SELECT cr_cse_id
, 0
, 'P'
, NULL
, NULL
, NULL
, NULL
, PARS_intrvention_catgy_cd
, PARS_intrvention_cd
, (SELECT COUNT(*) FROM xParsIntrvWindowC piw
WHERE piw.rev_key = cse.rev_key)

FROM CrCaseC cse
INNER JOIN xParsIntrvWindowC piw1
ON cse.rev_key = piw1.rev_key

 

[JamesLean]

Is there a reason you are not using a simple IDENTITY column in this situation? It would seem the most obvious solution.

 

[marksg]

Yes, because of the following scenario. Where Key1b is the sequence number I'm creating and Key1a and Key1b together make the primary key.

Key1a Key1b
Case 1 1
Case 1 2
Case 1 3
Case 2 1
Case 2 2
Case 3 1

 

[tlbroadbent]

You have two problems.

1) The PRIAMRY KEY constraint check will occur before[/] the TRIGGER fires and will prevent insertion of the rows.

2) You've coded the trigger to handle one row at a time. If more than one row is inserted, then the trigger will
attempt to use the same sequence number for all rows. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[marksg]

Is there anyway around these two issues, or do I need to find another way?

Thanks

 

[alfredp77]

if you are using SQL Server 2000, then you can use "INSTEAD OF" triggers. Check out this link to find out how to use it:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro01/html/sql01a17.asp