CODEWITHSUNDEEP
sqloracle-databaseplsqldatabase-trigger
stitch123
stitch123

Reputation: 307

Oracle trigger: SELECT INTO, no data found

I have the following table that describes which chemical elements each planet is composed of using percentage.

CREATE TABLE elem_in_planet
(
    id_planet INTEGER,
    element_symbol CHAR(2),
    percent_representation NUMBER CONSTRAINT NN_elem_in_planet NOT NULL,

    CONSTRAINT PK_elem_in_planet PRIMARY KEY (id_planet, element_symbol),
    CONSTRAINT FK_planet_has_elem FOREIGN KEY (id_planet) REFERENCES planet (id_planet),
    CONSTRAINT FK_elem_in_planet FOREIGN KEY (element_symbol) REFERENCES chemical_element (element_symbol)
);

I'm trying to make a trigger that warns users when they add a new element to a planet and the sum of elements in that planet exceeds 100%. I came up with this.

CREATE OR REPLACE TRIGGER elem_in_planet_check
    AFTER INSERT OR UPDATE ON elem_in_planet
    FOR EACH ROW
DECLARE
    sum_var NUMBER;
    PRAGMA autonomous_transaction;
BEGIN
    SELECT SUM(percent_representation)
    INTO sum_var
    FROM elem_in_planet
    WHERE id_planet = :NEW.id_planet
    GROUP BY id_planet;

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            sum_var := 0;

    IF sum_var > 100 THEN
        DBMS_OUTPUT.put_line('WARNING: Blah blah.');
    END IF;
END;
/

This code seems to throw the NO_DATA_FOUND exception every single time, even though I have inserted test data and when I run the SQL query alone, it works as expected.

I'm new to this and don't understand what I'm doing wrong.

Thank you for any advice.

Upvotes: 1

Views: 1248

Answers (1)

Belayer
Belayer

Reputation: 14861

You have NOT inserted the row into the table, 2 reasons.

  1. The trigger runs as part of the insert statement which has not completed. So the row does not exist.
  2. You specified "PRAGMA autonomous_transaction" (AKA the create untraceable bug here statement), did you previously get a mutating table exception. So you cannot see any data inserted/updated/deleted by the current transaction.Further if an error did occur the row would still be inserted as you did not raise an error or re-raise the existing error. I suggest you familiarize yourself with the PLSQL block structure. For now you may want to try:

You could use an after statement trigger or after statement section of compound trigger to make this test, do raise_application_error if sum > 100;

BTW as it stands your "if on sum_var > 100" runs only when an error occurs. Anything after the "EXCEPTION" and before END for that block runs only when a error occurs. 

create or replace trigger elem_in_planet_check
    after insert or update on elem_in_planet
declare 
   error_detected boolean := False;
begin
    for planet in 
        (
         select id_planet, sum_var
           from (select id_planet, sum(percent_representation) sum_var
                   from elem_in_planet
                  group by id_planet
                )
         where sum_var > 100
        )
    loop
       dbms_output.put_line('Planet ' || planet.id_planet || ' at '|| planet.sum_var || '% resources exceed 100%');  
       error_detected:= True; 
    end loop; 

    if error_detected then 
            Raise_application_error('-20001', 'Planet resources cannot exceed 100%'); 
    end if;

end elem_in_planet_check;

Upvotes: 1

Related Questions