CODEWITHSUNDEEP
oracle-databaseexceptionplsqltriggers
Dyin
Dyin

Reputation: 5366

Trigger to RAISE_APPLICATION_ERROR but execute inner commands

I'm working on the following PL/SQL trigger:

CREATE OR REPLACE TRIGGER trigger_1
BEFORE UPDATE ON worker
FOR EACH ROW
DECLARE
BEGIN
  IF :OLD.type = 'PRESIDENT' THEN
    INSERT INTO trigger_log VALUES (sysdate, 'Nope.', 'No change.');
    RAISE_APPLICATION_ERROR(-20111, 'Can not change!');
  END IF;
END;

Here, I want to cancel the UPDATE command on the worker table, when a PRESIDENT's payment is about to get changed. At the same time, I wish to log this command into table called trigger_log. The problem is, when I RAISE_APPLICATION_ERROR the UPDATE got cancelled, but the logging (INSERT INTO trigger_log) aswell. How can I RAISE_APPLICATION_ERROR or throw an EXCEPTION, but still have all commands to be run inside the TRIGGER?

Upvotes: 0

Views: 4404

Answers (3)

Dba
Dba

Reputation: 6639

You can call a stored procedure from your trigger. That stored procedure should be declared as PRAGMA AUTONOMOUS_TRANSACTION. Try like this,

    CREATE OR REPLACE 
    PROCEDURE log_error_p
    AS
         PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
         INSERT INTO trigger_log VALUES (SYSDATE, 'Nope.', 'No change.');
         COMMIT;
    END;

--

   CREATE OR REPLACE TRIGGER trigger_1
    BEFORE UPDATE ON worker
    FOR EACH ROW
    DECLARE
    BEGIN
         IF :OLD.TYPE = 'PRESIDENT' THEN
              log_error_p();
         RAISE_APPLICATION_ERROR(-20111, 'Can not change!');
         END IF;
    END;
    /

Upvotes: 2

Armunin
Armunin

Reputation: 996

You have to commit your INSERT-Statement before raising the error.

Maybe think about AUTONOMOUS_TRANSACTION

EDIT
As others have stated already, you should not and cannot commit inside a trigger (exception autonomous transaction). So think about using the solution of Tom Thomas or calling a logging-procedure/-package.

Upvotes: 2

Tom Thomas
Tom Thomas

Reputation: 629

First of all, never use a commit or a rollback inside the trigger. It's a coding standard. As for your question i think this is a better way than raise_application_error

CREATE OR REPLACE TRIGGER trigger_1
BEFORE UPDATE ON worker
REFERENCING OLD as o AND NEW as n
FOR EACH ROW
DECLARE
     InsertException EXCEPTION;
BEGIN
     IF o.TYPE = 'PRESIDENT' THEN
          RAISE InsertException;
     END IF;
EXCEPTION
     WHEN InsertException THEN
     INSERT INTO trigger_log VALUES (SYSDATE, 'Nope.', 'No change.');
     DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

Upvotes: -1

Related Questions