PL/SQL - Oracle triggers

GeekInterview.com > Interview Questions > Oracle > PL/SQL

Go To First | Previous Question | Next Question

PL/SQL | Question 207 of 241

Oracle triggers

can we issue rollback, commit in the trigger body. if we issue what is the result

Total Answers and Comments: 5

Last Update: May 31, 2008 Asked by: maheshveeragoni

Sponsored Links

Best Rated Answer

—

Submitted by: binurajnair

An autonomous transaction is an independent transaction which can be committed independent of other transactions. An autonomous transaction will be committed with out committing the other non-autonomous transactions are committed.

The following exercise will help you to understand the difference between autonomous and normal transactions.

Create a table for the exercise.

create table test_table (a varchar2(50));

Create a non-autonomous procedure which will insert one row to this table.

CREATE OR REPLACE PROCEDURE non_autonomous_proc
IS
BEGIN
INSERT INTO test_table
VALUES ('Non Autnomous Insert');

COMMIT;
END;

Now execute the following code

DECLARE
BEGIN
INSERT INTO test_table
VALUES ('Before Non Autnomous Insert');

non_autonomous_proc;

INSERT INTO test_table
VALUES ('After Non Autnomous Insert');

ROLLBACK;
END;

Now query the table

select * from test_table;

As expected 2 rows will be retrieved

A
--------------------------------------------------
Before Non Autnomous Insert
Non Autnomous Insert

Now we will see the case of autonomous transaction.

Clear the test_table

delete from test_table;
commit;

Create an autonomous procedure which will insert one row to this table.

CREATE OR REPLACE PROCEDURE autonomous_proc
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO test_table
VALUES ('Autnomous Insert');

COMMIT;
END;

Now Execute the following code

DECLARE
BEGIN
INSERT INTO test_table
VALUES ('Before Autnomous Insert');

autonomous_proc;

INSERT INTO test_table
VALUES ('After Autnomous Insert');

ROLLBACK;
END;

And query the table

select * from test_table;

This will show only one row

A
--------------------------------------------------
Autnomous Insert

Conclusion

1) The commit statement in the autonomous procedure will commit the DML operations in the autonomous procedure without commiting the transactions before that.

2) The rollback statement caused both the inserts before and after the autonomous transaction to be rolled back, but not the autonomous transaction

Above answer was rated as good by the following members:
sourena_1237, ratna82, kperumal75, johnjerry

February 21, 2008 07:21:27

binurajnair

Member Since: February 2008 Contribution: 15

RE: Oracle triggers

Commit and roll back statements are not possible from triggers. Although we can create triggers with COMMIT and ROLLBACK it will result in an error when the trigger has fired.

Ideally this is desirable since the original transaction may be rolled back and so the effects of trigger also should be rolled back.

However we can use commit if it is declared as an autonomous transaction. Generally that will be used for writing to log tables.

Is this answer useful? Yes | No

1 0

Overall Rating: +1

February 27, 2008 20:42:59

maheshveeragoni

Member Since: February 2008 Contribution: 2

RE: Oracle triggers

what is automonous tranction

Is this answer useful? Yes | No

February 28, 2008 08:11:36

binurajnair

Member Since: February 2008 Contribution: 15

RE: Oracle triggers

Create a table for the exercise.

create table test_table (a varchar2(50));

Create a non-autonomous procedure which will insert one row to this table.

CREATE OR REPLACE PROCEDURE non_autonomous_proc
IS
BEGIN
INSERT INTO test_table
VALUES ('Non Autnomous Insert');

COMMIT;
END;

Now execute the following code

DECLARE
BEGIN
INSERT INTO test_table
VALUES ('Before Non Autnomous Insert');

non_autonomous_proc;

INSERT INTO test_table
VALUES ('After Non Autnomous Insert');

ROLLBACK;
END;

Now query the table

select * from test_table;

As expected 2 rows will be retrieved

A
--------------------------------------------------
Before Non Autnomous Insert
Non Autnomous Insert

Now we will see the case of autonomous transaction.

Clear the test_table

delete from test_table;
commit;

Create an autonomous procedure which will insert one row to this table.

CREATE OR REPLACE PROCEDURE autonomous_proc
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO test_table
VALUES ('Autnomous Insert');

COMMIT;
END;

Now Execute the following code

DECLARE
BEGIN
INSERT INTO test_table
VALUES ('Before Autnomous Insert');

autonomous_proc;

INSERT INTO test_table
VALUES ('After Autnomous Insert');

ROLLBACK;
END;

And query the table

select * from test_table;

This will show only one row

A
--------------------------------------------------
Autnomous Insert

Conclusion

1) The commit statement in the autonomous procedure will commit the DML operations in the autonomous procedure without commiting the transactions before that.

2) The rollback statement caused both the inserts before and after the autonomous transaction to be rolled back but not the autonomous transaction

Is this answer useful? Yes | No

3 0

Overall Rating: +3

March 12, 2008 17:34:27

arbind1982

Member Since: August 2007 Contribution: 6

RE: Oracle triggers

we can use commit or rolback in trigger without error by using "PRAGMA AUTONOMOUS_TRANSACTION;" on declaration section

CREATE TRIGGER anniversary_trigger
BEFORE INSERT ON employees FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO anniversaries VALUES(TRUNC(:new.hire_date));
-- Only commits the preceding INSERT not the INSERT that fired
-- the trigger.
COMMIT;
EXCEPTION
-- If someone else was hired on the same day we get an exception
-- because of duplicate values. That's OK no action needed.
WHEN OTHERS THEN NULL;
END;
/

Is this answer useful? Yes | No

May 31, 2008 23:10:24

krishnaindia2007

Member Since: September 2007 Contribution: 854

RE: Oracle triggers

We can use commit and rollback in trigers using autonomous transactions.

- An atonomous transaction is an independent transaction initiated by another transaction.
- When an atonomous transaction is called the parent transaction is temporarly suspended.
- An atonomous transaction allows us to sub divide a transaction into multiple rollback and commit transactions.
- We can commit or rollback an autonomous transaction independent of parent transaction or without effecting parent transaction.
- It should be committed or rollback before return control to the calling transaction.

You can make a transaction as autonomous transaction by simply adding PRAGMA AUTONOMOUS_TRANSACTION in declarative part of the statement.

Is this answer useful? Yes | No

Go To Top

User Name		Remember Me?
Password

Question Bank