Can we use commit or rollback in trigger? If yes, then how. Please explain with a suitable example?
- 5 Answers
- 11989 Views
- HSBC, I also Faced
- E-Mail Answers
Answer Posted / lopa
We can use commit and roll back in a trigger using autonomous transaction.
CREATE TABLE at_test (
id NUMBER NOT NULL,
description VARCHAR2(50) NOT NULL
);
INSERT INTO at_test (id, description) VALUES (1, 'Description for 1');
INSERT INTO at_test (id, description) VALUES (2, 'Description for 2');
SELECT * FROM at_test;
ID DESCRIPTION
---------- --------------------------------------------------
1 Description for 1
2 Description for 2
2 rows selected.
Next, we insert another 8 rows using an anonymous block declared as an autonomous transaction, which contains a commit statement.
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
FOR i IN 3 .. 10 LOOP
INSERT INTO at_test (id, description)
VALUES (i, 'Description for ' || i);
END LOOP;
COMMIT;
END;
/
PL/SQL procedure successfully completed.
SELECT * FROM at_test;
ID DESCRIPTION
---------- --------------------------------------------------
1 Description for 1
2 Description for 2
3 Description for 3
4 Description for 4
5 Description for 5
6 Description for 6
7 Description for 7
8 Description for 8
9 Description for 9
10 Description for 10
10 rows selected.
SQL>
As expected, we now have 10 rows in the table. If we now issue a rollback statement we get the following result.
ROLLBACK;
SELECT * FROM at_test;
ID DESCRIPTION
---------- --------------------------------------------------
3 Description for 3
4 Description for 4
5 Description for 5
6 Description for 6
7 Description for 7
8 Description for 8
9 Description for 9
10 Description for 10
8 rows selected.
SQL>
The 2 rows inserted by our current session (transaction) have been rolled back, while the rows inserted by the autonomous transactions remain. The presence of the PRAGMA AUTONOMOUS_TRANSACTION compiler directive made the anonymous block run in its own transaction, so the internal commit statement did not affect the calling session. As a result rollback was still able to affect the DML issued by the current statement.
| Is This Answer Correct ? | 9 Yes | 1 No |
Post New Answer View All Answers
Write a sql select query that only returns each name only once from a table?
what is a tablespace? : Sql dba
What does plv msg allows you to do?
What is the need of merge statement?
What company owns postgresql?
how to enter characters as hex numbers? : Sql dba
how to use myisamchk to check or repair myisam tables? : Sql dba
How many joins in sql?
How would you pass hints to the sql processor?
What is mutating table error?
How many types of relationship are there?
How can we connect an Android App to an Oracle database and use the PL/SQL procedural code?
Does asenumerable execute the query?
How to write html code in pl sql?
How does a covering index work?
