Thursday, February 8, 2018

What is PRAGMA AUTONOMOUS TRANSACTION in PLSQL?







/*****************************************************************************
PRAGMA AUTONOMOUS_TRANSACTION
*****************************************************************************/
 The Child Code runs independently of its parent
 the Child code can commit or rollback and parent resumes
 the parent code can continue without affecting child work

 drop table my_test;
 CREATE TABLE my_test (
  id           NUMBER       NOT NULL,
  description  VARCHAR2(50) NOT NULL
);

INSERT INTO my_test (id, description) VALUES (1, 'Description for 1');
INSERT INTO my_test (id, description) VALUES (2, 'Description for 2');

DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  FOR i IN 3 .. 10 LOOP
    INSERT INTO my_test (id, description)
    VALUES (i, 'Description for ' || i);
  END LOOP;
  commit;
END;
/


select * from my_test;

rollback;

delete from my_test;




CREATE or REPLACE TRIGGER EMP_TRIGGER
    BEFORE
    INSERT ON emp
    FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    :new.ename := upper(:new.ename);
  commit;
END;

/

insert into emp   select * from scott.emp;

commit;

No comments:

Post a Comment