SpiritOfLennon
IS-IT--Management
Hi,
I have an an Oracle table that holds sequences for transactions.
Example of data
TRANTYPE1 00000001234
TRANTYPE2 00000005673
Certain transactions update both of the sequence numbers. Sometimes one if incremented more than once for an associated single increment of the other and yet there is no audit between the two sequences when there needs to be.
I have been trying to put a trigger on the table in order to catch the update of one sequence and then catch the updates of the other. Obviously it obtains the mutating trigger error. So here is my plan.
I want to set up a trigger that sets a creates a new sequence number something like
CREATE OR REPLACE TRIGGER SEQ_RECON_TRIG
BEFORE INSERT OR UPDATE
ON SEQUENCE_TAB
BEGIN
SEQ_RECON_PKG.NEWROWS := SEQ_RECON.NEXTVAL;
END;
Where SEQ_RECON_PKG is defined as
CREATE OR REPLACE PACKAGE SEQ_RECON_PKG
AS
TYPE SEQ IS TABLE OF VARCHAR2(30);
NEWROWS SEQ;
END;
I could then within the body of another trigger use
CREATE OR REPLACE TRIGGER SEQ_RECON2_TRIG
AFTER UPDATE OF KEY_VALUE ON SEQUENCE_TAB
FOR EACH ROW
WHEN (NEW.SEQUENCE = 'TRANTYPE1' or NEW.SEQUENCE = 'TRANTYPE2')
DECLARE
BEGIN
INSERT INTO SEQ_AUDIT_SB_TAB
( KEY , VALUE, NOW, NEW_SEQ ) VALUES ( :NEW.SEQUENCE, :NEW.SEQ_VALUE, SYSDATE, SEQ_RECON_PKG.NEWROWS );
EXCEPTION
WHEN OTHERS THEN
NULL;
END SEQ_RECON2_TRIG;
This would then hopefully tie back each update of the sequences to the same common sequence number within an audit table, however this does not work. For a start it does not allow me to set the package variable equal to the next sequence number and I'm sure that if I can resolve this it's going to complain about the use of this package variable.
Has anyone come across a similar problem and if so what is their solution or failing that can anyone see how to make my solution work. Any suggestions would be gratefully appreciated.
Regards SOL SOL
I'm only guessing but my guess work generally works for me.
I have an an Oracle table that holds sequences for transactions.
Example of data
TRANTYPE1 00000001234
TRANTYPE2 00000005673
Certain transactions update both of the sequence numbers. Sometimes one if incremented more than once for an associated single increment of the other and yet there is no audit between the two sequences when there needs to be.
I have been trying to put a trigger on the table in order to catch the update of one sequence and then catch the updates of the other. Obviously it obtains the mutating trigger error. So here is my plan.
I want to set up a trigger that sets a creates a new sequence number something like
CREATE OR REPLACE TRIGGER SEQ_RECON_TRIG
BEFORE INSERT OR UPDATE
ON SEQUENCE_TAB
BEGIN
SEQ_RECON_PKG.NEWROWS := SEQ_RECON.NEXTVAL;
END;
Where SEQ_RECON_PKG is defined as
CREATE OR REPLACE PACKAGE SEQ_RECON_PKG
AS
TYPE SEQ IS TABLE OF VARCHAR2(30);
NEWROWS SEQ;
END;
I could then within the body of another trigger use
CREATE OR REPLACE TRIGGER SEQ_RECON2_TRIG
AFTER UPDATE OF KEY_VALUE ON SEQUENCE_TAB
FOR EACH ROW
WHEN (NEW.SEQUENCE = 'TRANTYPE1' or NEW.SEQUENCE = 'TRANTYPE2')
DECLARE
BEGIN
INSERT INTO SEQ_AUDIT_SB_TAB
( KEY , VALUE, NOW, NEW_SEQ ) VALUES ( :NEW.SEQUENCE, :NEW.SEQ_VALUE, SYSDATE, SEQ_RECON_PKG.NEWROWS );
EXCEPTION
WHEN OTHERS THEN
NULL;
END SEQ_RECON2_TRIG;
This would then hopefully tie back each update of the sequences to the same common sequence number within an audit table, however this does not work. For a start it does not allow me to set the package variable equal to the next sequence number and I'm sure that if I can resolve this it's going to complain about the use of this package variable.
Has anyone come across a similar problem and if so what is their solution or failing that can anyone see how to make my solution work. Any suggestions would be gratefully appreciated.
Regards SOL SOL
I'm only guessing but my guess work generally works for me.
