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

Question

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"

pmdba · Accepted Answer · 2022-08-04 14:39:25Z

3

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

edited Aug 4, 2022 at 14:39

answered Aug 4, 2022 at 14:32

pmdba

7,0932 gold badges10 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

VeniceKing Over a year ago

Yes, this is the version I'm using: "Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0"

VeniceKing Over a year ago

I tested it in an Enterprise Edition version and it compiled OK, the trigger is working fine.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related