PL SQL trigger not functioning

Question

please help me with the with the following trigger:

    create or replace TRIGGER "TRG_ECO"
AFTER delete OR UPDATE or INSERT ON add_tree

  referencing new as new old as old
  for each row
  declare
  var_num  number(1) :=1; 
  var_tree_type number(1);


BEGIN
        dbms_output.put_line('tRIGGER ADD CONTACT TRRIGGERED: ');



   select tree_type 
          INTO var_tree_type
          FROM master
          where seq_id = :old.add_parent;
    dbms_output.put_line('tRIGGER TRRIGGERED: ' || :new.add_parent);
   if(var_tree_type=7) then 


        select 
               CASE
                    WHEN (:new.add_parent not in (select a.seq_id  
                    from mastera)) then 0
                    WHEN (:new.add_parent in (select a.seq_id  
                    from mastera)) then 1
                    ELSE 1

            end
        into var_num
        from dual;


          UPDATE masterSET contact = var_num where seq_id = :new.add_parent;

    end if;


    dbms_output.put_line('tRIGGER TRRIGGERED: ');

END TRG_ECO;

because when i try to delete, insert, update a row i get an error like:

Error report - SQL Error: ORA-01403: no data found ORA-06512: at "TOADM.TRG_ECO", line 11 ORA-04088: error during execution of trigger 'TOADM.TRG_ECO' 01403. 00000 - "no data found" *Cause: No data was found from the objects. *Action: There was no data from the objects which may be due to end of fetch. Elapsed: 00:00:00.032

When i update the code with

create or replace TRIGGER "TRG_ECO"
AFTER UPDATE or INSERT ON add_tree

and I put where seq_id = :new.add_parent; I get the error:

"SQL Error: ORA-04098: trigger 'TOADM.ECO' is invalid and failed re-validation
04098. 00000 -  "trigger '%s.%s' is invalid and failed re-validation"
*Cause:    A trigger was attempted to be retrieved for execution and was
           found to be invalid.  This also means that compilation/authorization
           failed for the trigger.
*Action:   Options are to resolve the compilation/authorization errors,
           disable the trigger, or drop the trigger."

NO_DATA_FOUND exception needs to be handled. When a select into statement returns no rows NO_DATA_FOUND exception is raise. Error message says it all. Second select statement looks redundant. The result of the case expression can be assigned directly to a var_num variable. — Nick Krasnov

jera jera · Accepted Answer · 2015-12-11T11:31:57

When triggered by a delete statement there are no :new values, likewise when triggered by an insert statement there are no :old values. This may be a problem here.

PL SQL trigger not functioning

1 Answers