Trigger errors ORA-04092 ORA-04088

Question

I created a trigger as below:

CREATE OR REPLACE TRIGGER trigger_test
AFTER INSERT ON trigger_1
FOR EACH ROW
DECLARE
t_identifier VARCHAR2(10);
t_name VARCHAR2(20);
BEGIN
t_identifier := (:NEW.IDENTIFIER);
t_name := (:NEW.NAME);
INSERT INTO trigger_2(IDENTIFIER,NAME)VALUES(t_identifier,t_name);
COMMIT;
END;

I am trying to insert a row in trigger_1

INSERT INTO trigger_1(IDENTIFIER,NAME)
VALUES('1234567','Vijay');

It is giving me the errors:

ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "LVSDBO46.TRIGGER_TEST", line 8
ORA-04088: error during execution of trigger 'LVSDBO46.TRIGGER_TEST'

Could nybody please help?

Answer 1

Just remove

COMMIT;

from the trigger code. Trigger execute in an ongoing transaction, so you cannot do a separate commit. When the transaction is commited, your insert in trigger_2 will also be commited.

Answer 2

Might I suggest shortening it a bit.

CREATE OR REPLACE TRIGGER trigger_test
  AFTER INSERT ON trigger_1
  FOR EACH ROW
  BEGIN
    INSERT INTO trigger_2 (IDENTIFIER,NAME) VALUES (:NEW.IDENTIFIER,:NEW.NAME);
  END;

Answer 3

In case you really really need to commit (chances are you don't have to, but just in case...) you can create a procedure with the AUTONOMOUS_TRANSACTION PRAGMA.