Pl/sql Trigger Question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Pl/sql Trigger Question

  1. #1
    Join Date
    Jan 2003
    Location
    Maryland, USA
    Posts
    12

    Pl/sql Trigger Question

    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;
    Frank

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    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.

    Code:
    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.

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    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.

  4. #4
    Join Date
    Jan 2003
    Location
    Maryland, USA
    Posts
    12

    Reply to gandoif989

    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;
    Frank

  5. #5
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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:
    Code:
    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:
    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;
    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.
    Ales
    The whole difference between a little boy and an adult man is the price of toys

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width