Mega Code Archive
Autonomous triggers
<![CDATA[
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> 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> drop table order_tran_coming_in;
Table dropped.
SQL>
SQL> drop table order_tab;
Table dropped.
SQL>
]]>