Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Error PLS-00049 on trigger

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
Answers
-
INTO :new.Clef_Var?
-
I took this line from an old trigger, but yeah i don't think this line is usefull
-
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
-
Ok, i searched but i don't really see where do i have to put the old value.
-
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.
-
Ref_equip is not in SIVO.EV_48H
Thank you Chris, i'll try in dynamic SQL