CODEWITHSUNDEEP
sqloracle-databasetriggersoracle-sqldeveloper
Diego Sanchez
Diego Sanchez

Reputation: 51

Trying to insert into a table after a raise_application error on Oracle doesn't works

I'm developing a trigger that focuses to avoid insert the date highest than the current date of the system. When I try to insert it into an audit table after a raise application error (for documental purpose), it doesn't work before or after neither of that custom exceptions.

SQL Query:

    --TRIGGER TO CONTROL THE DATE OF REGISTRATION OF A SERVICE, ITS TOTAL COST AND THE SERVICE TIME IN DAYS.
    --IF THE REGISTRATION DATE IS GREATER THAN THE CURRENT DATE, THE SERVICE WILL NOT BE ABLE TO REGISTER AND THE FAILED PROCESS WILL BE INSERTED IN THE AUDIT TABLE. DOING THIS PART OF THE SCRIPT, I REALIZED THAT THERE WAS A CLASS EXAMPLE WITH THIS
    --IF YOUR TOTAL COST IS GREATER THAN 60000000, A 10% DISCOUNT WILL BE MADE, OTHERWISE, THE FULL PRICE WILL BE CHARGED
    --IF THE SERVICE TIME IN DAYS IS GREATER THAN 1 MONTH (30 DAYS APPROXIMATELY), YOU WILL BE GIVEN AN ADDITIONAL DISCOUNT OF 5% ON THE TOTAL COST
    CREATE OR REPLACE TRIGGER TRG_CONTROL_SERVICIO
    BEFORE
    INSERT OR UPDATE
    ON SERVICIO
    FOR EACH ROW
    DECLARE
    V_COSTOTAL INT;
    V_DESCUENTO INT;
    BEGIN
        IF INSERTING THEN
           IF :NEW.FECHAREGISTRO > SYSDATE THEN
--FOLLOWING LINES WITH ERRORS
                   RAISE_APPLICATION_ERROR(-20000, 'FECHA INCORRECTA, INTENTE DE NUEVO');
                   INSERT INTO AUDITORIA VALUES(USER, SYSDATE, 'SERVICIO', 'INSERT FECHA INCORRECTA', 'SE EJECUTO EL TRG_CONTROL_SERVICIO'); 
           ELSE
              DBMS_OUTPUT.PUT_LINE('INSERTANDO UN SERVICIO');
           END IF;
           IF V_COSTOTAL > 60000000 THEN
                   V_DESCUENTO := 0.1 * V_COSTOTAL;
                   V_COSTOTAL := V_COSTOTAL - V_DESCUENTO;
           ELSE
              INSERT INTO AUDITORIA VALUES(USER, SYSDATE, 'SERVICIO', 'INTENTO DE DESCUENTO FALLIDO', 'SE EJECUTO EL TRG_CONTROL_SERVICIO');      
              --RAISE_APPLICATION_ERROR(-20000, 'NO SE PUEDE APLICAR EL DESCUENTO',TRUE);
           END IF;
        ELSIF UPDATING THEN
            IF :NEW.FECHAREGISTRO <> :OLD.FECHAREGISTRO AND :NEW.FECHAREGISTRO > SYSDATE THEN
                   INSERT INTO AUDITORIA VALUES(USER, SYSDATE, 'SERVICIOg', 'PROHIBIDO UPDATE FECHAREGISTRO', 'SE EJECUTO EL TRG_CONTROL_SERVICIO');            
                  --RAISE_APPLICATION_ERROR(-20000, 'FECHA INCORRECTA, NO SE PUEDE MODIFICAR ESTA FECHA',TRUE);
        ELSE
            DBMS_OUTPUT.PUT_LINE('ACTUALIZANDO UN EMPLEADO');
        END IF;
    END IF;
    END;
    
    INSERT INTO SERVICIO (IDSERVICIO, FECHAREGISTRO, DESCRIPCION, COSTOTOTAL, FK_IDRENTA, FK_IDVENDEDOR, FK_IDCLIENTE, PROVISIONES, TIEMPOSERVICIODIAS)
    VALUES (7,TO_DATE('05/11/2021','DD/MM/YYYY'),'SERVICIO COMPLETO',50000000,3,3,3,'LLANTAS',60);
    
    
    SELECT * 
    FROM SERVICIO; --SERVICE TABLE
    
    SELECT * 
    FROM AUDITORIA; --AUDTI TABLE

Upvotes: 0

Views: 532

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

Raising an error in the trigger causes the triggering statement to fail and be rolled back. That would rollback the insert into the audit table even if you fix the immediate issue where the raise_application_error call is before the insert into the audit table.

You could create a separate procedure that inserts into the audit table and is defined to use an autonomous transaction

create or replace procedure log_audit( p_user in varchar2,
                                       p_dt   in date,
                                       p_str1 in varchar2,
                                       p_str2 in varchar2,
                                       p_str3 in varchar2 )
as
  pragma autonomous_transaction;
begin
  insert into AUDITORIA ( <<list columns here>> )
    VALUES( p_user, p_dt, p_str1, p_str2, p_str3 );      
  commit;
end;

You'd then call that from within your trigger before raising the error

      IF :NEW.FECHAREGISTRO > SYSDATE THEN
               log_audit( USER, 
                          SYSDATE, 
                          'SERVICIO', 
                          'INSERT FECHA INCORRECTA', 
                          'SE EJECUTO EL TRG_CONTROL_SERVICIO'); 
               RAISE_APPLICATION_ERROR(-20000, 'FECHA INCORRECTA, INTENTE DE NUEVO');

Upvotes: 1

Related Questions