asked on

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

Sean Stuber

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.

pippen

ASKER

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
/

SOLUTION

Alex [***Alex140181***]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pippen

ASKER

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

SOLUTION

Wasim Akram Shaik

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pippen

ASKER

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.

Alex [***Alex140181***]

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