CODEWITHSUNDEEP
oracle-databaseplsqlauto-incrementdatabase-triggerdatabase-sequence
Mlikaon
Mlikaon

Reputation: 25

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

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)
)

Upvotes: 0

Views: 2112

Answers (2)

Mlikaon
Mlikaon

Reputation: 25

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.

Upvotes: 0

user330315
user330315

Reputation:

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

Upvotes: 2

Related Questions