Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott
SQL> CREATE TABLE COMMIT_ON_TRIGGER(ID NUMBER, NAME VARCHAR2(10));
Table created
SQL> CREATE TABLE CHK_COMMIT_ON_TRIGGER(ID NUMBER);
Table created
SQL>
SQL> CREATE OR REPLACE TRIGGER CHK_COMMIT_TRIGGER
2 AFTER INSERT ON COMMIT_ON_TRIGGER FOR EACH ROW
3 BEGIN
4 INSERT INTO CHK_COMMIT_ON_TRIGGER VALUES(:NEW.ID);
5 COMMIT;
6 END;
7 /
Trigger created
SQL> INSERT INTO COMMIT_ON_TRIGGER VALUES (1,'TEST');
INSERT INTO COMMIT_ON_TRIGGER VALUES (1,'TEST')
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "SCOTT.CHK_COMMIT_TRIGGER", line 3
ORA-04088: error during execution of trigger 'SCOTT.CHK_COMMIT_TRIGGER'
SQL> CREATE OR REPLACE TRIGGER CHK_COMMIT_TRIGGER
2 AFTER INSERT ON COMMIT_ON_TRIGGER FOR EACH ROW
3 DECLARE
4 PRAGMA AUTONOMOUS_TRANSACTION;
5 BEGIN
6 INSERT INTO CHK_COMMIT_ON_TRIGGER VALUES(:NEW.ID);
7 COMMIT;
8 END;
9 /
Trigger created
SQL> INSERT INTO COMMIT_ON_TRIGGER VALUES (1,'TEST');
1 row inserted
SQL> SELECT * FROM COMMIT_ON_TRIGGER;
ID NAME
---------- ----------
1 TEST
SQL>
SQL> SELECT * FROM CHK_COMMIT_ON_TRIGGER;
ID
----------
1
SQL>
NOTE THAT ONLY IN AUTONOMOUS TRIGGER ARE ALLOWED FOR USES COMMIT/ROLLBACK AND AFTER PERFORMING TRANSACTION COMMIT REQUIRED FOR ON SESSION TRANSACTION. COMMIT IN TRIGGER ONLY SAVE TRANSACTION PERFORMED INSIDE TRIGGER.
No comments:
Post a Comment