Error PLS-00049 on trigger

MathBob

Hello,

I'm trying to create a trigger that put data on a table to an other table. I have data that arrives on real-time on the table EV_48H and i have to put them automatically on the tables that are named 'EVV_'+Ref_equip.

I have an error PLS-00049 for lines 17 and 20. I'm not accostumed with PL/SQL.

    

Code:

create or replace
TRIGGER "SIVO"."NEWtrigger3EV_48H"
BEFORE INSERT
ON SIVO.EV_48H
FOR EACH ROW



begin
SELECT clef_var
INTO :new.Clef_Var
FROM sivo.c_variable 
WHERE Ref_Var= :new.Ref_Var;

  -- Conversion des formats Date-Heure en DateHeure oracle
  :New.EV_DATEAUTO := to_date(:New.EV_DATE || ' ' || :New.EV_HEURE, 'DD/MM/YY HH24:MI:SS');
  
  IF clef_var is not null then
  SELECT Ref_Equip 
  INTO :New.Ref_Equip 
  FROM sivo.C_Equip 
  WHERE Ref_Var= :new.Ref_Var;
  INSERT INTO SIVO.||'EVV_'|| :New.Ref_Equip (Clef_Var, Date1, Valeur) 
  VALUES ( :New.Clef_Var, :New.Ev_DateAuto, :New.Ev_Valeur ); 

  ELSE
  INSERT INTO SIVO.EV_48H_VAR_INCONNUES (REF_VAR, EV_DATE, EV_HEURE, EV_VALEUR) 
  VALUES ( :New.REF_VAR, :New.EV_DATE, :New.EV_HEURE, :New.EV_VALEUR); 
    
  end if;
END;

• Erreur(17,8): PLS-00049: variable attachée (bind variable) erronée 'NEW.REF_EQUIP' (bind variable invalid)
• Erreur(20,3): PL/SQL: SQL Statement ignored
• Erreur(20,21): PL/SQL: ORA-00903: nom de table non valide (table name invalid -> because the bind variable is invalid?)
• Erreur(20,32): PLS-00049: variable attachée (bind variable) erronée 'NEW.REF_EQUIP'  (bind variable invalid)

So the error is in this part of the code:

  IF clef_var is not null then
  SELECT Ref_Equip 
  INTO :New.Ref_Equip 
  FROM sivo.C_Equip 
  WHERE Ref_Var= :new.Ref_Var;
  INSERT INTO SIVO.||'EVV_'|| :New.Ref_Equip (Clef_Var, Date1, Valeur) 
  VALUES ( :New.Clef_Var, :New.Ev_DateAuto, :New.Ev_Valeur ); 

Thank you for the help

Jagadekara

INTO :new.Clef_Var?

MathBob

I took this line from an old trigger, but yeah i don't think this line is usefull

HuaMin Chen

The error is regarding bad bind variable and you can refer to old and new values of the table, within the trigger, using

:old.column1

:new.column1

MathBob

Ok, i searched but i don't really see where do i have to put the old value.

Chris Hunt

Is there a column in SIVO.EV_48H called REF_EQUIP? If not, it's not going to like :NEW.REF_EQUIP.

Either way, it's not going to like this:

INSERT INTO SIVO.||'EVV_'|| :New.Ref_Equip

If you don't know the name table you're going to be inserting into at compile time, you'll have to use dynamic SQL to build the SQL statement at run time. Look up EXECUTE IMMEDIATE for examples.

MathBob

Ref_equip is not in SIVO.EV_48H

Thank you Chris, i'll try in dynamic SQL