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
- 13411 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 are the authentication modes in sql server? : Sql dba
Can we insert in sql function?
what are all types of user defined functions? : Sql dba
What is multiple partition?
Why primary key is required?
How do you identify a primary key?
What is the usage of the distinct keyword?
What pl/sql package consists of?
Can a commit statement be executed as part of a trigger?
What happens when a trigger is associated to a view?
What is the use of index in sql?
Why is sql*loader direct path so fast?
Can we write ddl statements in functions?
what are different types of collation sensitivity? : Sql dba
Explain unique key in sql.
