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
This is what I did already, like I wrote above the first Edit section. The exact error is the first quotation. — Mlikaon
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

a_horse_with_no_name a_horse_with_no_name · Accepted Answer · 2016-06-17T13:50:22

: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;
/

Oracle : “parsing failed” error when creating an auto_increment trigger

EDIT

EDIT 2

2 Answers