Problem running my sql trigger code: ORA-24344: Success with Compilation Error

Question

I'm trying to create a trigger which updates a value VALOR_RESTANTE on PRESTAMO each time there's an insert on PAGO. As you can see in te code below:

CREATE OR REPLACE TRIGGER pago_a_prest
AFTER INSERT ON PAGO
FOR EACH ROW WHEN ( new.CODIGO_PREST IS NOT NULL )
DECLARE
current_year NUMBER(4);
current_mounth NUMBER(2);
current_date NUMBER(2);
BEGIN
SELECT TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) FROM DUAL INTO current_year;
SELECT TO_NUMBER(TO_CHAR(SYSDATE,'MM')) FROM DUAL INTO current_mounth;
SELECT TO_NUMBER(TO_CHAR(SYSDATE,'DD')) FROM DUAL INTO current_day;
UPDATE PRESTAMO SET VALOR_RESTANTE=VALOR_RESTANTE+(VALOR_TOTAL*TASA_INTERES/100)*(current_year-(SELECT TO_NUMBER(TO_CHAR(FECHA_INICIAL,'YYYY')) FROM PRESTAMO));
UPDATE PRESTAMO SET VALOR_RESTANTE=VALOR_RESTANTE-:NEW.VALOR_PAGO WHERE CODIGO_PREST = :NEW.CODIGO_PREST;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END pago_a_prest;

Once I run it, I get this error:

ORA-06512: en "SYS.WWV_DBMS_SQL_APEX_200100", línea 581
ORA-06512: en "SYS.DBMS_SYS_SQL", línea 1658
ORA-06512: en "SYS.WWV_DBMS_SQL_APEX_200100", línea 567
ORA-06512: en "APEX_200100.WWV_FLOW_DYNAMIC_EXEC", línea 2127

I already verified all column names and the code for dates and don't know what should I do.

Thanks!

Answer 1

• Get rid of all those local variables. You just need the value for current_year for which an extra SQL Statement is not needed.
• There are two Update Statements those should be combined( I just showed a sample, but you need perform this should be rearranged due to your own or company's rules ), and the combined one needs a Where Clause to restrict the data to be updated.
• It's beneficient to apply NVL(..,0) function to each monetary value in order to avoid the miscalculations for the null values. Moreover, Rounding with a scale of 2 needed for those monetary values after multiplication and division operations.
• No subquery such as (SELECT TO_NUMBER(TO_CHAR(FECHA_INICIAL,'YYYY')) FROM PRESTAMO) needed. Since the column FECHA_INICIAL's value is already been selected from the concerned table(PRESTAMO) which is the one being updated.

Therefore, you can rewrite the trigger as

   CREATE OR REPLACE TRIGGER pago_a_prest
     AFTER INSERT ON PAGO
     FOR EACH ROW
     WHEN (NEW.CODIGO_PREST IS NOT NULL)       
   BEGIN
     UPDATE PRESTAMO
        SET VALOR_RESTANTE = NVL( VALOR_RESTANTE, 0)  - NVL( :NEW.VALOR_PAGO, 0) +
                             ROUND(
                                   ( NVL( VALOR_TOTAL, 0) * NVL( TASA_INTERES, 0) / 100) *
                                   ( TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY') ) 
                                   - TO_NUMBER( TO_CHAR(FECHA_INICIAL, 'YYYY') ) )
                            ,2)                             
      WHERE CODIGO_PREST = :NEW.CODIGO_PREST;
   EXCEPTION
     WHEN OTHERS THEN
       ROLLBACK;
   END;