I'd not use autonomous transactions here, at least for two reasons:
- A record is inserted into the audit table, if the transaction that fired the trigger is either commited or rolled back. This might cause inconsistencies.
- Autonomous transaction is autonomous, it doesn't see changes made by statement that fired the trigger, as the "outer" transaction is not commited yet. Thus, we have inconsistent result again.
What I would do:
- create a table BOP_DETAIL_AUDIT_SUM with two columns: stg_rows, stg_amount. It will have only one row, fill it with actual values.
- rewrite the original trigger to update the row in the BOP_DETAIL_AUDIT_SUM table:Now we have assured the consistency of the tables NDW_BOP_DETAIL_M_TEST and BOP_DETAIL_AUDIT_SUMCode:CREATE OR REPLACE TRIGGER BOPSUMARRY_sum AFTER INSERT OR UPDATE ON NDW_BOP_DETAIL_M_TEST for each row begin if inserting then update BOP_DETAIL_AUDIT_SUM set stg_rows=stg_rows+1, stg_amount=stg_amount+:new.amount; end if; if updating then update BOP_DETAIL_AUDIT_SUM set stg_amount=stg_amount-:old.amount+:new.amount; end if; end;
- create a trigger on the BOP_DETAIL_AUDIT_SUM table that inserts a record into audit table:There is one bad thing about this solution - the updating of the BOP_DETAIL_AUDIT_SUM table causes serialization of transactions - in case of concurrent transactions, all others will wait for the first one to commit.Code:CREATE OR REPLACE TRIGGER BOPSUMARRY after update of BOP_DETAIL_AUDIT_SUM for each row begin INSERT INTO BOP_DETAIL_AUDIT ( AUDIT_NUM, STG_ROWS, STG_AMOUNT, RENEW_DATE) VALUES (BOP_SEQ.NEXTVAL, :new.STG_ROWS, :new.STG_AMOUNT, sysdate); end;
The code is not tested.




Reply With Quote