I have a trigger which stores log record even if the trigger fails. Declared a pragma transaction to handle it. Though it's working but not working for failed trigger condition, no rows inserted in the sales log table.

create or replace trigger sales_trg_all
after insert or update or delete on sales_tbl   for each row
declare action varchar2(10);
v_salesid number;
begin    
if inserting then 
insert into sales_audit (salesid,salesdt,type) values (:NEW.salesid,:NEW.salesdt,'I');
ELSIF updating then 
insert into sales_audit (salesid,salesdt,type) values (:NEW.salesid,:NEW.salesdt,'U');
ELSIF deleting then 
insert into sales_audit (salesid,salesdt,type) values (:OLD.salesid,:OLD.salesdt,'D');
END IF;
EXCEPTION WHEN OTHERS THEN
IF inserting then 
action :='I';
v_salesid :=NEW.salesid;
ELSIF updating then 
action :='U';
v_salesid :=NEW.salesid;
ELSIF deleting then 
action :='D';
v_salesid :=OLD.salesid;
END IF;
Pragma autonomous_transaction;
insert into sales_log (salesID,type) values (salesid,action);
commit;
END;

Are all the blocks defined in right order? Not sure what's going wrong. Any help is appriciated.

I have a trigger which stores log record even if the trigger fails. Declared a pragma transaction to handle it. Though it's working but not working for failed trigger condition, no rows inserted in the sales log table.

create or replace trigger sales_trg_all
after insert or update or delete on sales_tbl   for each row
declare action varchar2(10);
v_salesid number;
begin    
if inserting then 
insert into sales_audit (salesid,salesdt,type) values (:NEW.salesid,:NEW.salesdt,'I');
ELSIF updating then 
insert into sales_audit (salesid,salesdt,type) values (:NEW.salesid,:NEW.salesdt,'U');
ELSIF deleting then 
insert into sales_audit (salesid,salesdt,type) values (:OLD.salesid,:OLD.salesdt,'D');
END IF;
EXCEPTION WHEN OTHERS THEN
IF inserting then 
action :='I';
v_salesid :=NEW.salesid;
ELSIF updating then 
action :='U';
v_salesid :=NEW.salesid;
ELSIF deleting then 
action :='D';
v_salesid :=OLD.salesid;
END IF;
Pragma autonomous_transaction;
insert into sales_log (salesID,type) values (salesid,action);
commit;
END;

Are all the blocks defined in right order? Not sure what's going wrong. Any help is appriciated.

• oracle-database
• triggers

Share Improve this question Follow edited Mar 25 at 15:51 synccm2012 asked Mar 25 at 14:47 synccm2012synccm2012 49722 gold badges1010 silver badges2626 bronze badges 3

• 1 I suspect it's just a typo in the question, but you have "Pragma autonomous_transcation;". It should be "Pragma autonomous_transaction;". – Bob Jarvis - Слава Україні Commented Mar 25 at 15:18
• @Bob, That's true. – synccm2012 Commented Mar 25 at 15:23
• 1 Your pragma is in the wrong place. It must be in the declare section – Paul W Commented Mar 25 at 15:42

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

• autonomous_transcation should be autonomous_transaction.
• saesID probably should be salesID
• NEW should be :NEW
• OLD should be :OLD
• The PRAGMA should be in the DECLARE block.
• Your code should be formatted neatly so you can see at a glance what the relative level of each block is.
• I have no idea what you are trying to achieve with the exception handler - it probably isn't required. If you do have a need for an exception handler then you should not catch OTHERS (as it catches everything and masks all the errors your code raises), instead you should only specific errors you are expecting.

If you fix and simplify everything then you get to something like:

CREATE OR REPLACE TRIGGER sales_trg_all
  after insert or update or delete on sales_tbl
  FOR EACH ROW
DECLARE
  v_action  SALES_AUDIT.TYPE%TYPE;
  v_salesid SALES_AUDIT.SALESID%TYPE;
  v_salesdt SALES_AUDIT.SALESDT%TYPE;
  PRAGMA autonomous_transaction;
BEGIN
  IF inserting THEN
    v_action  := 'I';
    v_salesid := :NEW.salesid;
    v_salesdt := :NEW.salesdt;
  ELSIF updating THEN
    v_action  := 'U';
    v_salesid := :NEW.salesid;
    v_salesdt := :NEW.salesdt;
  ELSIF deleting THEN 
    v_action  := 'D';
    v_salesid := :OLD.salesid;
    v_salesdt := :OLD.salesdt;
  END IF;
  insert into sales_log (salesID,type)
    values (v_salesid, v_action);

  insert into sales_audit (salesid,salesdt,type)
    values (v_salesid, v_salesdt, v_action);
  COMMIT;
END;
/

fiddle

---

I am trying to get the records in the audit table if the trigger runs successfully. If it fails then there should not be any record in audit( this is used for only success) but it should insert log records in the log table(looking for pragma only here).

Use two triggers:

CREATE OR REPLACE TRIGGER sales_trg_audit
  after insert or update or delete on sales_tbl
  FOR EACH ROW
DECLARE
  v_action  SALES_AUDIT.TYPE%TYPE;
  v_salesid SALES_AUDIT.SALESID%TYPE;
  v_salesdt SALES_AUDIT.SALESDT%TYPE;
BEGIN
  IF inserting THEN
    v_action  := 'I';
    v_salesid := :NEW.salesid;
    v_salesdt := :NEW.salesdt;
  ELSIF updating THEN
    v_action  := 'U';
    v_salesid := :NEW.salesid;
    v_salesdt := :NEW.salesdt;
  ELSIF deleting THEN 
    v_action  := 'D';
    v_salesid := :OLD.salesid;
    v_salesdt := :OLD.salesdt;
  END IF;
  insert into sales_audit (salesid,salesdt,type)
    values (v_salesid, v_salesdt, v_action);
END;
/

CREATE OR REPLACE TRIGGER sales_trg_log
  after insert or update or delete on sales_tbl
  FOR EACH ROW
DECLARE
  v_action  SALES_AUDIT.TYPE%TYPE;
  v_salesid SALES_AUDIT.SALESID%TYPE;
  v_salesdt SALES_AUDIT.SALESDT%TYPE;
  PRAGMA autonomous_transaction;
BEGIN
  IF inserting THEN
    v_action  := 'I';
    v_salesid := :NEW.salesid;
    v_salesdt := :NEW.salesdt;
  ELSIF updating THEN
    v_action  := 'U';
    v_salesid := :NEW.salesid;
    v_salesdt := :NEW.salesdt;
  ELSIF deleting THEN 
    v_action  := 'D';
    v_salesid := :OLD.salesid;
    v_salesdt := :OLD.salesdt;
  END IF;
  insert into sales_log (salesID,type)
    values (v_salesid, v_action);
  COMMIT;
END;
/

fiddle

Requirement: Trigger success (audit table got inserted). Trigger failed(log table got inserted).

Don't use a trigger. Write a package:

CREATE PACKAGE sales_pkg IS
  PROCEDURE insert_sales(
    i_salesid SALES_TBL.SALESID%TYPE,
    i_salesdt SALES_TBL.SALESDT%TYPE
  );

  PROCEDURE update_sales(
    i_salesid SALES_TBL.SALESID%TYPE,
    i_salesdt SALES_TBL.SALESDT%TYPE
  );

  PROCEDURE delete_sales(
    i_salesid SALES_TBL.SALESID%TYPE
  );
END sales_pkg;
/

CREATE PACKAGE BODY sales_pkg IS
  PROCEDURE insert_sales(
    i_salesid SALES_TBL.SALESID%TYPE,
    i_salesdt SALES_TBL.SALESDT%TYPE
  )
  IS
  BEGIN
    INSERT INTO sales_tbl (salesid, salesdt)
      VALUES (i_salesid, i_salesdt);

    INSERT INTO sales_audit (salesid, salesdt, type)
      VALUES (i_salesid, i_salesdt, 'I'); 
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN -- Or other appropriate exceptions
      INSERT INTO sales_log (salesid, type)
        VALUES (i_salesid, 'I');
  END;

  PROCEDURE update_sales(
    i_salesid SALES_TBL.SALESID%TYPE,
    i_salesdt SALES_TBL.SALESDT%TYPE
  )
  IS
  BEGIN
    UPDATE sales_tbl
    SET   salesdt = i_salesdt
    WHERE salesid = i_salesid;

    INSERT INTO sales_audit (salesid, salesdt, type)
      VALUES (i_salesid, i_salesdt, 'U'); 
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN -- Or other appropriate exceptions
      INSERT INTO sales_log (salesid, type)
        VALUES (i_salesid, 'U');
  END;

  PROCEDURE delete_sales(
    i_salesid SALES_TBL.SALESID%TYPE
  )
  IS
    v_salesdt SALES_TBL.SALESDT%TYPE;
  BEGIN
    DELETE FROM sales_tbl
    WHERE salesid = i_salesid
    RETURNING salesdt INTO v_salesdt;

    INSERT INTO sales_audit (salesid, salesdt, type)
      VALUES (i_salesid, v_salesdt, 'D'); 
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN -- Or other appropriate exceptions
      INSERT INTO sales_log (salesid, type)
        VALUES (i_salesid, 'D');
  END;
END sales_pkg;
/

Then when you want to INSERT/UPDATE/DELETE call the package procedures (and never call the DML functions directly on the underlying tables).