Answer Posted / mrityunjay singh

Normally, the usage of a commit or a rollback is not recommended in triggers. But there are certain situations in which we have to use commit or rollback in triggers. One of the prevalent usages of commit in trigger is to avoid the mutating table error encountered during trigger execution prior to 11g. Other way to avoid this error is use of compound trigger introduced in Oracle 11g. To use database control statements in triggers, we have to use compiler directive ‘pragma autonomous_transaction’. When we define trigger as autonomous, it becomes independent and does not belong to current transaction. Since trigger is not part of current transaction, use of commit is allowed in the trigger when declared with pragma. This holds true for any package, procedure and/or function defined with pragma directive. Let us start with an example. We will create two tables and trigger to explain the scenario. Example is based on the one mentioned in oracle documentation.
CREATE TABLE TEST
(
COL1 NUMBER(9) PRIMARY KEY,
COL2 VARCHAR2(30),
COL3 NUMBER(5)
);

CREATE TABLE AUDIT_TEST
(
COL1 NUMBER(9),
COL2 VARCHAR2(30),
OLD_COL3 NUMBER(5),
NEW_COL3 NUMBER(5),
COL4 DATE
);

INSERT INTO TEST
SELECT
rownum,’HELLO..’||to_char(rownum), rownum*1000
FROM user_Tables;

Now create following trigger with commit statement in the trigger body.

CREATE OR REPLACE TRIGGER TUA_TEST
AFTER UPDATE OF COL3
ON TEST
FOR EACH ROW
BEGIN
INSERT INTO audit_test(col1,col2,new_col3,old_col3,col4)
VALUES(: old.col1, : old.col2, : new.col3, : old.col3,sysdate);
COMMIT;
END;
/

Trigger will get created without any error. But whenever we update test table, we run into following error.


SQL> UPDATE TEST
2 SET COL3 = 2000
3 WHERE col1 = 1;
UPDATE TEST
*
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at “SCOTT.TUA_TEST”, line 5
ORA-04088: error during execution of trigger ‘SCOTT.TUA_TEST’
Now let us create same trigger with pragma autonomous_transaction and execute the update statement again.


CREATE OR REPLACE TRIGGER TUA_TEST
AFTER UPDATE OF COL3
ON TEST
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO audit_test(col1,col2,new_col3,old_col3,col4)
VALUES(: old.col1, : old.col2, :new.col3, : old.col3,sysdate);
COMMIT;
END;
/


/* update statement*/
SQL> update test
2 set col3 = 2000
3 where col1 = 1;

1 row updated.
This time update went through fine since trigger is declared autonomous As mentioned earlier, it is not a routine requirement to use control statements in triggers but under certain scenarios where we have to use control statements, this is the way to use it.

What is the difference between union and union all command?

740

---

Explain what is rdbms?

780

---

What is the best sql course?

720

---

what are the disadvantages of mysql? : Sql dba

761

---

Can a procedure in a package be overloaded?

754

---

---

What is java sql connection?

691

---

What does t sql mean?

702

---

Why stored procedure is better than query?

690

---

Are null values same as that of zero or a blank space?

731

---

Table A Table B 1 1 2 1 3 1. Union & union all --> A Union B , A Union all B 2. Minus , Intersect --> A minus B , B Minus A , A Intersect B 3. Joins A join B , A Left Join B A Right Join B , A full Join B 4. %Type - Uses & Benifit 5. Truncate & Delete 6. Pragma Autonomus Transaction 7. how to Perform DDL from function or procedure 8. Can we have DML inside Function 9. Rank & Dense Rank diffrence 10. Water Mark in Oracle 11. Index , Can we have index in all column of table if no then why ?

743

---

What are aggregate functions in sql?

839

---

What are different categories of sql commands?

808

---

What is pl sql architecture?

682

---

What is sql table?

688

---

What are analytical functions in sql?

728

---