Autonomous triggers : Trigger and Transaction « Trigger « Oracle PL/SQL Tutorial






SQL>
SQL>
SQL> CREATE TABLE order_tab(
  2     order_id     NUMBER(10) PRIMARY KEY,
  3     order_date   DATE NOT NULL,
  4     total_qty    NUMBER,
  5     total_price  NUMBER(15,2),
  6     supp_id      NUMBER(6)
  7  );

Table created.

SQL>
SQL> CREATE TABLE order_tran_coming_in(
  2     order_id              NUMBER(10)  NOT NULL,
  3     order_date            DATE        NOT NULL,
  4     tran_coming_in_date   DATE        NOT NULL,
  5     success_flag          VARCHAR2(1) DEFAULT 'N' NOT NULL);

Table created.

SQL>
SQL>
SQL>
SQL> create or replace trigger bi_order_tab
  2  before insert on order_tab for each row
  3  declare
  4    pragma autonomous_transaction;
  5  begin
  6    insert into order_tran_coming_in values (:NEW.order_id,
  7                                             :NEW.order_date,
  8                                             SYSDATE,
  9                                             'N');
 10    commit;
 11  end;
 12  /

Trigger created.

SQL> create or replace trigger ai_order_tab
  2  after insert on order_tab for each row
  3  declare
  4    pragma autonomous_transaction;
  5  begin
  6    update order_tran_coming_in
  7    set    success_flag = 'Y'
  8    where  order_id = :NEW.order_id;
  9    commit;
 10  end;
 11  /

Trigger created.

SQL> BEGIN
  2    INSERT INTO order_tab VALUES(102,SYSDATE,NULL,NULL,1001);
  3    INSERT INTO order_tab VALUES(103,SYSDATE,NULL,NULL,1001);
  4    INSERT INTO order_tab VALUES(103,SYSDATE,NULL,NULL,1001);
  5  EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
  6    ROLLBACK;
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> drop table order_tran_coming_in;

Table dropped.

SQL>
SQL> drop table order_tab;

Table dropped.

SQL>








28.19.Trigger and Transaction
28.19.1.Autonomous triggers
28.19.2.Mark trigger with PRAGMA AUTONOMOUS_TRANSACTION
28.19.3.Commit in trigger
28.19.4.Call PRAGMA AUTONOMOUS_TRANSACTION procedure
28.19.5.SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
28.19.6.Transaction with 'pragma autonomous_transaction'