Oracle tutorial : Autonomous Transactions in Oracle 11g PL SQL-PRAGMA
What is pragma autonomous_transaction
An autonomous transaction is an independent transaction to the main or parent transaction. If an Autonomous transaction is started by another transaction it is not nested, but independent of parent transaction.
PRAGMA autonomous_transaction
1)In Oracle session, all of the changes made to data are part of a single
transaction.
2)An autonomous transaction is an independent transaction started within another
transaction (the main transaction).
3)Autonomous transactions allow you to temporarily suspend the main
transaction, perform additional SQL operations, commit or rollback those
operations separately, then resume the main transaction.
4)To define an autonomous transaction, we can use a PRAGMA statement
PRAGMA AUTONOMOUS_TRANSACTION;
CREATE TABLE log_data
( empno NUMBER(6),
userid VARCHAR2(30),
create_date DATE );
CREATE OR REPLACE TRIGGER add_log
BEFORE INSERT OR UPDATE ON employee FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION; — This statement made this autonomous this trigger
BEGIN
INSERT INTO log_data
VALUES (:new.id, USER, SYSDATE);
COMMIT;
END;
/
Insert into EMPLOYEE
(ID, NAME, CITY, SALARY, DEPT_NO)
Values
(20, ‘MY Tech Query’, ‘Mumbai’, 20000, 1)
ROLLBACK;
SELECT * FROM EMPLOYEE
SELECT * FROM LOG_DATA
[ Ссылка ]
For more tutorial please visit #techquerypond
[ Ссылка ]
[ Ссылка ]
[ Ссылка ]
oracle transaction
pragma autonomous_transaction
