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 log shipping? : Sql dba

1113

---

How can we implement rollback or commit statement in a trigger?

1034

---

Explain what is a database?

1085

---

What is rank function in sql?

1026

---

How to combine two stored procedures in sql?

1234

---

create SQL (both DML/DDL) statements appropriate for the creation of relational structures & constraints and other objects for a given case study, the population of these tables and the manipulation (querying/updating) of the stored data. 2. Create, develop and use the PL / SQL Program Units Procedures, Functions as a progression towards Object Oriented Relational Database Programming. 3. Package a collection of logically related Procedures and Functions together to further move towards development of Objects which reflect the principle of Data Abstraction whereby only an Object specified in the Interface is accessible to the end user. 4. Select, create, and use appropriate Database Triggers to impose agreed specific constraints on a Database Table. 5. Provide a full and detailed evaluation which includes a comprehensive test execution plan and its implementation for each of the above. Consider the following case study: Perilous Printing is a medium size printing company that does work for book publishers throughout UK. The company currently has 10 offices, most of which operate autonomously, apart from salaries, which are paid by the head office. Currently the sharing and communication of data, is carried out using multi- user networked access to a centralised RDBMS. Perilous Printing jobs consist of printing books or part of books. A printing job requires the use of materials, such as paper and ink, which are assigned to a job via purchase orders. Each printing job may have several purchase orders assigned to it. Likewise, each purchase order may contain several purchase order items. The following tables form part of the transactional RDB that the company uses: office (office_no, o_addr, o_telno, o_faxno, mgr_nin) staff (staff_no, nin, fname, lname, s_addr, s_telno, gender, dob, position, taxcode, salary, office_no) publisher (pub_no, p_name, p_city, p_telno, p_faxno, credit_code, office_no) book_job (job_no, pub_no, job_date, job_desc, job_type, job_status, supervisor_nin) purchase_order (job_no, po_no, po_date) po_item (job_no, po_no, it_no, qty) item (it_no, it_desc, amt_in_stock, price) office contains details of each office and the office number (office_no) is the key. Each office has a Manager represented by the manager’s national insurance number (mgr_nin). staff contains details of staff; the staff_no is the key. The office that the member of staff works from is given by office_no. publisher contains details of publisher and the publisher number (pub_no) is the key. Publishers are registered with the nearest office in their country, given by office_no, and they are given a credit code that can have the values “AA”, “AB”, “BB”, “BC”, “CC”, “CD” and “DD”. If a publisher is to be deleted then not only the publisher’s entry from the publisher table will have to be removed but all the data associated with the particular supplier has to be deleted too book_job contains details of publishing jobs and the job number (job_no) is the key. The publisher is given by the publisher number (pub_no) and the supervisor for the job by supervisor_nin. The job type can be either null or urgent; whereas the job_status can be “ongoing” or “completed” purchase_order contains details of the purchase orders for each job and the combination of job number and a purchase order number (job_no, po_no) form the key. Each printing job may have several purchase orders assigned to it. item contains details of all materials that can be used in printing jobs and the item number (it_no) is the key. po_item contains details of the items on the purchase order and (job_no, po_no, it_no) forms the key. In the above given database schema, descriptions are strings of characters (at most 30 characters long), any dates (except the job_ date) stored cannot be after the current system date, and quantities and prices are assumed to be non-negative numbers.

2784

---

Can cursors be part of a trigger body?

1641

---

Write a sql query to convert all character to uppercase after hypen.

1438

---

What normalization means?

976

---

Why use stored procedures?

1090

---

What is the difference between rollback and rollback to statements?

1060

---

What is snowflake sql?

1071

---

What is inner join in sql?

1079

---

What plvcmt and plvrb does in pl/sql?

1320

---

How many tables can a sql database have?

977

---