Pl/sql Trigger Question

[polaris_ws]

I am new hand in PL/SQL and seek help here. I created the following trigger. The purpose of the trigger is to insert one row count number and one row summarized amount into a audit table. After I insert or update new rows into the mutating table, it always insert more rows than one count row into table. Please help me to identify the problems in my code. Thanks. Here is the code.

CREATE OR REPLACE TRIGGER BOPSUMARRY
AFTER INSERT OR UPDATE ON NDW_BOP_DETAIL_M_TEST
DECLARE

STG_ROWS NUMBER;
STG_AMOUNT NUMBER;
RENEW_DATE DATE;


BEGIN
SELECT COUNT(NDW_TRANS_NO)STG_ROWS, SUM(AMOUNT)STG_AMOUNT, SYSDATE
INTO STG_ROWS, STG_AMOUNT, RENEW_DATE
FROM NDW_BOP_DETAIL_M_TEST;

INSERT INTO BOP_DETAIL_AUDIT (AUDIT_NUM, STG_ROWS, STG_AMOUNT,
RENEW_DATE)
VALUES (BOP_SEQ.NEXTVAL, STG_ROWS, STG_AMOUNT, RENEW_DATE);

END BOPSUMMARRY;

[gandolf989]

You should take the code out of this trigger and create a new procedure with the code that you call from this trigger. You will then need to have "PRAGMA AUTONOMOUS TRANSACTION;" in the declare section of the procedure.

I.E.

CREATE OR REPLACE PROCEDURE new_proc
AS
 PRAGMA AUTONOMOUS TRANSACTION;
 STG_ROWS NUMBER;
 STG_AMOUNT NUMBER;
 RENEW_DATE DATE;
BEGIN

 SELECT COUNT(NDW_TRANS_NO)STG_ROWS, SUM(AMOUNT)STG_AMOUNT, SYSDATE
 INTO STG_ROWS, STG_AMOUNT, RENEW_DATE
 FROM NDW_BOP_DETAIL_M_TEST;

 INSERT INTO BOP_DETAIL_AUDIT (
 AUDIT_NUM, STG_ROWS, STG_AMOUNT, RENEW_DATE)
 VALUES (BOP_SEQ.NEXTVAL, STG_ROWS, STG_AMOUNT, RENEW_DATE);

END new_proc;
/

CREATE OR REPLACE TRIGGER BOPSUMARRY
AFTER INSERT OR UPDATE ON NDW_BOP_DETAIL_M_TEST
DECLARE
BEGIN
 new_proc;
END BOPSUMMARRY;
/

I haven't tested it so there may be typos or omissions.

[gandolf989]

I should mention when you select from a table within the trigger that is firing on that table you will get a mutating table error. By moving the code to a procedure and creating an autonomous transaction you remove this limitation because the transaction firing the trigger is allowed to end and the transaction querying the data can run without effecting the primary transaction. There is a lot of information out there concerning mutating tables and triggers. Specifically what you can and can't do in a trigger.

[polaris_ws]

Gandoif989:

Thank you so much for your help. I implemented your code into Oracle. During the inserting data into table, it generated this error " ORA-06519 active autonomous transaction detected and rolled back". Here are the code for procedure and trigger. I will continue to work on it tommorow. Thanks a lot.


CREATE OR REPLACE PROCEDURE BOP_procedure
AS

PRAGMA AUTONOMOUS_TRANSACTION;
STG_ROWS NUMBER;
STG_AMOUNT NUMBER;
RENEW_DATE DATE;

BEGIN

SELECT COUNT(NDW_TRANS_NO) STG_ROWS, SUM(AMOUNT) STG_AMOUNT, SYSDATE
INTO STG_ROWS, STG_AMOUNT, RENEW_DATE
FROM NDW_BOP_DETAIL_M_TEST;

INSERT INTO BOP_DETAIL_AUDIT ( AUDIT_NUM, STG_ROWS, STG_AMOUNT, RENEW_DATE)
VALUES (BOP_SEQ.NEXTVAL, STG_ROWS, STG_AMOUNT, RENEW_DATE);

END BOP_procedure;
**********************************************************

CREATE OR REPLACE TRIGGER BOPUPDATE
AFTER INSERT OR UPDATE ON NDW_BOP_DETAIL_M_TEST
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
BOP_procedure;
COMMIT;
END BOPUPDATE;

[ales]

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:

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;

Now we have assured the consistency of the tables NDW_BOP_DETAIL_M_TEST and BOP_DETAIL_AUDIT_SUM
- create a trigger on the BOP_DETAIL_AUDIT_SUM table that inserts a record into audit table:

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;

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.
The code is not tested.