How to Define a Trigger on Commit in Oracle

How to define a trigger ON COMMIT in Oracle?

There is no ON COMMIT trigger mechanism in Oracle. There are workarounds however:

1. You could use a materialized view with ON COMMIT REFRESH and add triggers to this MV. This would allow you to trigger the logic when a base table has been modified at the time of commit. If the trigger raises an error, the transaction will be rolled back (you will lose all uncommited changes).

2. You can use DBMS_JOB to defer an action to after the commit. This would be an asynchronous action and may be desirable in some cases (for example when you want to send an email after the transaction has been successful). If you roll back the primary transaction, the job will be cancelled. The job and the primary session are independent: if the job fails the main transaction will not be rolled back.

In your case, you could probably use option (1). I personnaly don't like to code business logic in triggers since it adds a lot of complexity but technically I think it would be doable.

Does an insert trigger need a commit statement

Not only do triggers not need a COMMIT you can't put one in: a trigger won't compile if the body's code includes a COMMIT (or a rollback).

This is because triggers fire during a transaction. When the trigger fires the current transaction is still not complete. As COMMIT terminates a transaction allowing them in triggers would break the unit of work.

So changes executed in a trigger are committed (or rolled back) by the owning transaction which issued the DML that fired the trigger.

---

It is true that triggers can run under the PRAGMA AUTONOMOUS_TRANSACTION, in which case they must have a COMMIT. But this is an edge case, as there are few meaningful uses of nested transactions in Oracle.

Using commit in Trigger in Oracle 11g

No, you do not. The session that made the change to the table on which the trigger is placed issues the commit (or rollback), and that applies to all changes made by that session.

How can i force trigger commit the transaction

A trigger can not commit (or rollback) a transaction. If you need to ignore any error of the called procedure, you need to add an exception handler:

CREATE OR REPLACE TRIGGER TRG_MYTRIGGER
BEFORE UPDATE OR INSERT ON MYTAB
FOR EACH ROW 
DECLARE 
  MY_STATUS NUMBER(10);
BEGIN

  begin 
     MY_PROCEDURE(PARAMS..., MY_STATUS); -- THIS IS OPTIONAL
  exception 
    when others then 
      my_status := -42; -- signal an error through the status value
  end;

  :NEW.MY_STATUS := MY_STATUS;
END;
/

How to use COMMIT in Procedure called by trigger

You can not have a COMMIT inside a trigger. Your UPDATE will be committed as soon as the INSERT to the table1 is committed.

But to achieve what you want you can use an autonomous transaction. For example,

CREATE OR REPLACE TRIGGER mytrg
AFTER INSERT ON table1 FOR EACH ROW

DECLARE    
   PRAGMA AUTONOMOUS_TRANSACTION;    
BEGIN    
   myschema.myproc(:new.ID, :new.NAME, :new.TYPE_CODE, :new.LANGUAGE);    
   COMMIT;    
END;

---

Related Topics