Interested to Buy Any Domain ? << Click Here >> for more details...
Can we use commit or rollback in trigger? If yes, then how. Please explain with a suitable example?
- 5 Answers
- 13883 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
what happens if null values are involved in expressions? : Sql dba
What is bind variable in pl sql?
What is sql entity?
What are the indexing methods?
What are the types of subqueries?
What is rownum in sql?
Explain the steps needed to create the scheduled job?
What are pl sql procedures?
What are tables in sql?
What is an intersect?
What is delete command in sql?
What is memory optimized?
Can we insert in sql function?
How to write pl sql program in mysql command prompt?
Is oracle sql free?
