ORA-04098: trigger is invalid and failed re-validation

I'm trying to add an auto increment field in oracle using a trigger. I used this article as a reference: http://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle

It created the sequence (though it did not store it in the table script) and created the trigger, but when I try to add a new row to the table (either manually or using an insert script) I get this error:

ORA-04098: trigger is invalid and failed re-validation
pippen.NET Software DeveloperAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
sdstuberConnect With a Mentor Commented:
9.5 isn't a valid oracle database version.

Your trigger should have a semicolon after the END

CREATE OR REPLACE TRIGGER cep_dcs_trig
BEFORE INSERT ON cep_dcs
FOR EACH ROW
BEGIN
  SELECT cep_dcs_seq.NEXTVAL
  INTO   :new.cep_id
  FROM   dual;
END;
/ 

Open in new window


also, you need to create the cep_dcs_seq sequence
0
 
sdstuberCommented:
The sequence won't be part of the table script because the sequence is its own object.

The trigger in that link is valid for the table and sequence defined in that link.

If your trigger has an error, please post your table DDL and trigger code.

Also, what version of the database are you using?  In 12c you can create an auto-increment column without the trigger.
0
 
pippen.NET Software DeveloperAuthor Commented:
We have Oracle 9.5

CREATE TABLE xx.CEP_DCS
(
  CEP_ID                   NUMBER               NOT NULL,
  STUDENT_KEY              NUMBER,
  ISP_ID                   NUMBER,
  COUNTY_CODE              VARCHAR2(2 BYTE),
  SSN                      NUMBER,
  FIRST_NAME               VARCHAR2(15 BYTE),
  MIDDLE_INITIAL           VARCHAR2(1 BYTE),
  LAST_NAME                VARCHAR2(15 BYTE),
  CASE_NUMBER              VARCHAR2(10 BYTE),
  DATE_OF_BIRTH            DATE,
  GUARDIAN_FIRST_NAME      VARCHAR2(15 BYTE),
  GUARDIAN_MIDDLE_INITIAL  VARCHAR2(1 BYTE),
  GUARDIAN_LAST_NAME       VARCHAR2(15 BYTE),
  ADDRESS                  VARCHAR2(71 BYTE),
  CITY                     VARCHAR2(20 BYTE),
  STATE                    VARCHAR2(2 BYTE),
  ZIP                      VARCHAR2(5 BYTE),
  CERTIFIED_FOSTER_YN      VARCHAR2(1 BYTE)
)
TABLESPACE xx
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;


CREATE OR REPLACE TRIGGER xx.cep_dcs_trig
BEFORE INSERT ON xx.CEP_DCS FOR EACH ROW
BEGIN
  SELECT cep_dcs_seq.NEXTVAL
  INTO   :new.cep_id
  FROM   dual;
END
/

-----------------------------------------------------------------------

CREATE OR REPLACE TRIGGER cep_dcs_trig
BEFORE INSERT ON cep_dcs
FOR EACH ROW
BEGIN
  SELECT cep_dcs_seq.NEXTVAL
  INTO   :new.cep_id
  FROM   dual;
END
/
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Alexander Eßer [Alex140181]Connect With a Mentor Software DeveloperCommented:
Does the sequence "cep_dcs_seq" even exists?!
btw: why do you create the trigger "twice" (CREATE OR REPLACE TRIGGER cep_dcs_trig )?!
0
 
pippen.NET Software DeveloperAuthor Commented:
Sorry, that was Toad. It's Oracle 10G.

ok. That fixed the trigger. (missing semi-colon.) Thanks!

My load script runs now, but it doesn't insert anything. The table is still empty. (after committing and refreshing.)

Here is the load script that now runs without error, but does not insert anything:

insert into CEP_DCS
(select
null cep_id,
null student_key,
null isp_id,
trim(substr(DATAROW,1,2)),
trim(substr(DATAROW,3,9)),
upper(trim(substr(DATAROW,12,15))),
upper(trim(substr(DATAROW,27,1))),
upper(trim(substr(DATAROW,28,15))),
trim(substr(DATAROW,43,10)),
TO_DATE(trim(substr(DATAROW,55,4) || substr(DATAROW,53,2)),'MMDDRR'),
null,  --upper(trim(substr(DATAROW,59,15))),
null,  --upper(trim(substr(DATAROW,74,1))),
null,  --upper(trim(substr(DATAROW,75,15))),
null,  --upper(trim(substr(DATAROW,90,71))),
upper(trim(substr(DATAROW,161,20))),
upper(trim(substr(DATAROW,181,2))),
upper(trim(substr(DATAROW,183,5))),
'Y'
from CEP_DCS_RAW
--where rownum < 20
);
0
 
Wasim Akram ShaikConnect With a Mentor Commented:
Is the select retrieving data??.. Check the select statement output..!!!
0
 
pippen.NET Software DeveloperAuthor Commented:
Another problem is that I had the cep_id set to NOT NULL, and had to change that, and I was working in a copy of the database where the table I was loading from was empty.

I think it should work now. I will post back and award points when it is done.
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
Just as a sidenote / btw: if the table/view "CEP_DCS_RAW" is based on a flat file, like a CSV, using SQLLDR would definitely be faster and more suitable for this kind of data loading...
0
All Courses

From novice to tech pro — start learning today.