INSERT Trigger that modifies Primary Key

Question

INSERT Trigger that modifies Primary Key

Joe Shurpin

SSC Enthusiast

Points: 186
More actions
November 4, 2004 at 7:56 am

#86803

Hi.
I have the following situation:
I have two tables, ARItems (Primary Key = RefNo) and ARTrans (Two part Primary Key = RefNo, SeqNo) where RefNo of ARTrans is a foreign key to ARItems. In ARItems there is a column LastSeqNo which keeps track of the last SeqNo used in ARTrans for this ARItems row. When I insert a new ARTrans row, I need to increment LastSeqNo in ARItems and set SeqNo = LastSeqNo in ARTrans.
I am attempting to do this by an Insert Trigger on ARTrans:
---------------------------------------------------------------------------------------------------
CREATE TRIGGER trNextSeqNo ON ARTrans AFTER INSERT
AS
BEGIN
IF (SELECT COUNT(1) FROM INSERTED) > 0
  BEGIN
   UPDATE ARItems
    SET LastSeqNo = LastSeqNo + 1
    FROM INSERTED i JOIN ARItems a ON a.RefNo = i.Refno
   UPDATE ARTrans
    SET SeqNo = a.LastSeqNo
    FROM INSERTED i
    JOIN ARItems a ON a.RefNo = i.Refno
    WHERE ARTrans.RefNo = a.Refno
  END
ELSE
  BEGIN
   ROLLBACK TRAN
  END
END
---------------------------------------------------------------------------------------------------
For a given RefNo, if there are as yet no rows in ARTrans and I insert a row, it works fine. LastSeqNo in the corresponding ARItems row is bumped up by one and SeqNo in ARTrans is set to this number. However, if there already exists an ARTrans row for this RefNo (with SeqNo something other than '123') and I attempt to add a new one by doing
---------------------------------------------------------------------------------------------------
insert into ARTrans
(Refno, SeqNo, TC, TranDate, Amount)
VALUES ('1046670','123','I','11/01/2004',convert smallmoney,'75.99'))
---------------------------------------------------------------------------------------------------
I get the following:
----------------------------------------------------------------------------------------------------
(1 row(s) affected)
Server: Msg 2627, Level 14, State 1, Procedure trNextSeqNo, Line 13
Violation of PRIMARY KEY constraint 'PK_ARTrans'. Cannot insert duplicate key in object 'ARTrans'.
The statement has been terminated.
----------------------------------------------------------------------------------------------------
How am I inserting a duplicate key with the UPDATE statement (Line 13)? When I do something like
UPDATE ARTrans SET SeqNo = 123 WHERE RefNo = 1046670
for an existing ARTrans row, it makes the change to SeqNo with no complaints.
I am very new to SQL Server and I'm probably missing something very basic here. Still, I'd appreciate it if someone would point out what it is. And if this type of trigger is not the way to do what I'm trying to do, I would appreciate any other suggestions.
Thanks in advance,
Joe Shurpin

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Well, just like the name says, an "After Insert" trigger fires after the Insert has taken place. So in your case, the trigger doesn't fire at all, because the PRIMARY KEY CONSTRAINT violation.

I assume you're doing this within a stored procedure. Why don't you determine all necessary information first and then do the INSERT? That way you don't need to rely on triggers.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 2

You could use an INSTEAD OF INSERT trigger to calculate the next sequence number:

CREATE TRIGGER trNextSeqNo ON ARTrans INSTEAD OF INSERT

AS

BEGIN

INSERT INTO ARTrans (RefNo, SeqNo, ...)

SELECT i.RefNo, ISNULL(NewSeq, 1), ...

FROM inserted I

LEFT JOIN (

SELECT RefNo, MAX(SeqNo) + 1 AS NewSeq

FROM ARTrans GROUP BY RefNo) S ON I.RefNo = S.RefNo

END

This works as long as you don't insert multiple records for the same RefNo in the same command. Another difference is that this always assigns the next available SeqNo; if you want a sequence that persists even when recent ARTrans records have been deleted you'll need a different approach.

But I think an INSTEAD OF INSERT trigger of some sort is what you're looking for.

Joe Shurpin SSC Enthusiast Points: 186 More actions · Answer 3

Thanks Frank and Scott. After a while I realized that the approach I was taking wouldn't work for me. It would have been fine if I always added only one record at a time, but I also need this routine for a conversion of a table that has many rows for a given RefNo. What I ended up doing was to create a stored procedure that accepts the columns in ARTrans (except for SeqNo) as input, adds the row, and bumps up LastSeqNo in ARItems. For the conversion, I call the stored procedure using cursors and it seems to work just fine.

Thanks again,

Joe Shurpin