pl/sql-oracle, trigger before insert. Update some values from a select-CodePudding

I'm new to oracle and I'm stuck with a trigger that needs to update some values before inserting the row, those values need to be obtained from a select. I'm confused when to use NO_DATA_FOUND or SQL%FOUND to prevent the no data found exception in the select statements, particularly in the select that obtains multiple values and some of them can be null and some don't.

This is the code I have right now, but I'm getting an error:

create or replace NONEDITIONABLE TRIGGER IS_INSERT_OSCILOSPROTECCION_RELE 
BEFORE INSERT ON IS_OSCILOSPROTECCION 
referencing OLD as old NEW as new
FOR EACH ROW

DECLARE 
    v_IDMODELORELE VARCHAR2(50);
    v_IDRELE VARCHAR2(50);
    v_NOMBRERELE VARCHAR2(50);
    v_DESCRIPCIONRELE VARCHAR2(200);
    v_NUMEROSERIERELE VARCHAR2(50);

BEGIN
    BEGIN
        select IDRELE into v_IDRELE from IS_PROTECCION where IDPROTECCION = :new.IDPROTECCION;
    EXCEPTION WHEN NO_DATA_FOUND THEN
        v_IDRELE := NULL;
    END;
    
    IF v_IDRELE is not null THEN

        select IDMODELORELE, NOMBRE, DESCRIPCION, NUMEROSERIE into v_IDMODELORELE, v_NOMBRERELE, v_DESCRIPCIONRELE, v_NUMEROSERIERELE from RELE where IDRELE = v_IDRELE;
        IF SQL%FOUND THEN            
            :new.IDMODELORELE := v_IDMODELORELE;
            :new.NOMBRERELE := v_NOMBRERELE;
            :new.DESCRIPCIONRELE := v_DESCRIPCIONRELE;
            :new.NUMEROSERIERELE := v_NUMEROSERIERELE;
        END IF;
        
    END IF;
END;

Error report - ORA-00604: error occurred at recursive SQL level 1 ORA-12954: The request exceeds the maximum allowed database size of 12 GB. 00604. 00000 - "error occurred at recursive SQL level %s"

CodePudding user response：

You're most likely using the XE version of Oracle, and you have attempting to exceed the maximum amount of data it supports. There's nothing wrong with your trigger - you just don't have room for more data, so the insert fails.

You will need to delete some data from the same table, or reduce the size of the tablespace by freeing up other unused space. The following article has ideas on how to reclaim unused space: https://oracle-base.com/articles/misc/reclaiming-unused-space