Can we use commit or rollback in trigger? If yes, then how. Please explain with a suitable example?
- 5 Answers
- 11463 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
How to convert comma separated string to array in pl/sql?
What does joining a thread mean?
How do you use join?
Can we rename a column in the output of sql query?
The select into statement is most often used to create backup copies of tables or for archiving records?
What is pl/sql table? Why it is used?
What is difference between pls_integer and integer?
What are different sql data types?
What is pl sql and why it is used for?
What does count (*) do in sql?
What is a field in a database?
How to start the command-line sql*plus?
What is the sql case statement?
What is a temporal table?
What is record variable?
