Oracle : "parsing failed" error when creating an auto_increment trigger

Question

I'm trying to make an auto_increment trigger for the IDs of an Oracle database.
After some research, I found a way to write one using a sequence and a before insert trigger.

Problem is, when I execute the trigger, I have the following error :

Parsing failed for:

CREATE OR REPLACE TRIGGER AUTO_INC_PDE_ITINERAIRE
BEFORE INSERT
ON PDE_ITINERAIRE
FOR EACH ROW
BEGIN
SELECT PDE_ITINERAIRE_ID_SEQUENCE.NEXTVAL
INTO

If I use the following command :

select * from SYS.USER_ERRORS where name = 'AUTO_INC_PDE_ITINERAIRE';

It returns the following output :

Line 3 | Pos 10 | PLS-00201: identifier 'NEW.PDE_ITINERAIRE' must be declared
Line 2 | Pos 03 | PL/SQL: SQL statement ignored
Line 4 | Pos 03 | PL/SQL: ORA-00904 invalid identifier

Here is the full query for the trigger :

CREATE OR REPLACE TRIGGER AUTO_INC_PDE_ITINERAIRE
BEFORE INSERT 
ON PDE_ITINERAIRE
FOR EACH ROW
BEGIN
SELECT PDE_ITINERAIRE_ID_SEQUENCE.NEXTVAL
INTO   :NEW.PDE_ITINERAIRE.ID_PDE_ITINERAIRE
FROM   dual;
END;
/

I'm not really used to Oracle's triggers, so could someone help me finding out what is wrong in my trigger ?

Thanks for your time

EDIT

I changed the trigger from your advice

CREATE OR REPLACE TRIGGER AUTO_INC_PDE_ITINERAIRE
BEFORE INSERT 
ON PDE_ITINERAIRE
FOR EACH ROW
BEGIN
    :NEW.ID_PDE_ITINERAIRE := PDE_ITINERAIRE_ID_SEQUENCE.NEXTVAL;
END;
/

I still have the same error output though.

More informations :
--Oracle is v11.
--TOra 3 is used as IDE.

EDIT 2

Here is the DDL as asked :

CREATE TABLE "GEOMAP"."PDE_ITINERAIRE"
( "ID_PDE_ITINERAIRE" NUMBER(11,0) NOT NULL ENABLE,
"NOM_ITINERAIRE" VARCHAR2(255) NOT NULL ENABLE,
"LONGUEUR" NUMBER(15,4),
"INSEE_DEPART" VARCHAR2(5),
"INSEE_ARRIVEE" VARCHAR2(5),
"TYPE_ITINERAIRE" VARCHAR2(30),
"TYPE_BALISAGE" VARCHAR2(30),
"COULEUR_BALISAGE" VARCHAR2(55),
"NOM_TOPO_GUIDE" VARCHAR2(255),
"ANNEE_TOPO_GUIDE" VARCHAR2(4),
"DATE_DERNIER_ENTRETIEN" DATE,
"PERIODICITE_PREVUE" VARCHAR2(30),
"DATE_PROCHAIN_ENTRETIEN" DATE,
"ORGANISME_ENTRETIEN" VARCHAR2(60),
"OBSERVATIONS_ENTRETIEN" VARCHAR2(30),
"CREATEUR" VARCHAR2(55),
"COUT_TOTAL" VARCHAR2(50),
"DATE_DECISION_CP" DATE,
"SUBVENTION_ITINERAIRE" NUMBER(8,2),
"SUBVENTION_TOPO" NUMBER(8,2),
"OBSERVATIONS_ADMIN" VARCHAR2(255),
"HEBERGEMENT" VARCHAR2(30),
"MONUMENTS" VARCHAR2(30),
"OBSERVATIONS_TOURISTIQUES" VARCHAR2(30),
"GEOMETRIE" "MDSYS"."SDO_GEOMETRY" ,
"COMMUNE_DEPART" VARCHAR2(55),
"COMMUNE_ARRIVEE" VARCHAR2(55),
"FICHIER_TOPO_GUIDE" VARCHAR2(255)
)

try: select PDE_ITINERAIRE_ID_SEQUENCE.NEXTVAL into :NEW.ID_PDE_ITINERAIRE from dual; If that doesn't work, post the exact error message — tbone
– tbone, Commented Jun 17, 2016 at 14:44
This is what I did already, like I wrote above the first Edit section. The exact error is the first quotation. — Mlikaon
– Mlikaon, Commented Jun 20, 2016 at 6:55
No, you actually have :NEW.PDE_ITINERAIRE.ID_PDE_ITINERAIRE in your code, I just removed the reference to PDE_ITINERAIRE and have :NEW.ID_PDE_ITINERAIRE . The error suggests it cannot resolve the identifier PDE_ITINERAIRE (identifier 'NEW.PDE_ITINERAIRE' must be declared). Simple to try and see if it fixes the problem anyway. — tbone
– tbone, Commented Jun 20, 2016 at 12:45

user330315 · Accepted Answer · 2016-06-17 13:50:22Z

2

:new is a record containing all columns of the trigger's table, so you can't include the table name when referencing it:

:NEW.PDE_ITINERAIRE.ID_PDE_ITINERAIRE should be :NEW.ID_PDE_ITINERAIRE

Additionally, you don't need the select, you can simply assign the value (at least with any supported version of Oracle):

CREATE OR REPLACE TRIGGER AUTO_INC_PDE_ITINERAIRE
BEFORE INSERT 
ON PDE_ITINERAIRE
FOR EACH ROW
BEGIN
   :NEW.ID_PDE_ITINERAIRE := PDE_ITINERAIRE_ID_SEQUENCE.NEXTVAL;
END;
/

answered Jun 17, 2016 at 13:50

user330315

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

2 Comments

Mlikaon Over a year ago

Thank you for the early reply, I just updated the query the way you advised. I still have the same error output though. Btw my Oracle is version 11.

gvenzl Over a year ago

The error suggests that there is no such column in the table, maybe a typo? Can you please add the table DDL as well.

Mlikaon · Accepted Answer · 2016-06-20 07:19:08Z

0

It is fixed ! I finally downloaded Oracle SQL Developer to check my trigger in another IDE, and it proposed me to link a value (NEW) when i executed it. This last trick did the job.

I assume either Tora does not execute pl/sql properly, or maybe I missed a declaration for :NEW and SQL Developer fixed it.

Thank you for your help.

answered Jun 20, 2016 at 7:19

Mlikaon

252 silver badges7 bronze badges

Collectives™ on Stack Overflow

Oracle : "parsing failed" error when creating an auto_increment trigger

EDIT

EDIT 2

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

EDIT

EDIT 2

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related