Before Insert TRigger Mutates - Help

[hariprasad]

The following Trigger Mutates.
If we add Pragma Autonomous Transaction, It will not mutate.

Question, We want the Claimnumber+Seqnumber as unique
(IN Table they are not defined as Uniq due to certail other problems)

Now when too many records are inserted , if we have
Pragma Autonomous Transaction the seq number is not updated properly .

Is there the soulution other than pragma autonomous, and having proper seqnumber( Same principle as Order#, Line number of OrderDetail table)

when a new line is added line number should increase if order is existing if not start with 1

Hope this helps




TRIGGER "ACCOUNTING".TBI$AcctRecDetail
BEFORE INSERT ON AcctRecDetail
FOR EACH ROW
DECLARE
lnSeqNumber AcctRecDetail.SeqNumber%TYPE;
lnARDetID AcctRecDetail.ARDetID%TYPE;
BEGIN
SELECT NVL(MAX(SeqNumber), 0) + 1 INTO lnSeqNumber
FROM AcctRecDetail
WHERE ClaimNumber = :NEW.ClaimNumber;
:NEW.SeqNumber := lnSeqNumber;
IF :NEW.ARDetID IS NULL THEN
SELECT Seq$AcctRecDetail.NextVal INTO lnARDetID FROM Dual;
:NEW.ARDetID := lnARDetID;
END IF;
END;