在Oracle上出现RAISE_APPLICATION错误后尝试插入到表中不起作用 [英] Trying to insert into a table after a raise_application error on Oracle doesn't works

问题描述

我正在开发一个触发器，其重点是避免插入早于系统当前日期的日期。当我试图在RAISE应用程序错误之后将其插入到审核表中时(出于文档目的)，它在这两个自定义异常之前或之后都不起作用。

SQL查询：

    --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

推荐答案

在触发器中引发错误会导致触发语句失败并被回滚。即使您修复了raise_application_error调用位于插入到审计表之前的直接问题，这也会将插入回滚到审计表。

您可以创建一个插入到审计表中并定义为使用自主事务的单独过程

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;

然后在引发错误之前从触发器中调用该函数

      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');


  

这篇关于在Oracle上出现RAISE_APPLICATION错误后尝试插入到表中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！