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;
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment