Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-07-21
8
Medium Priority
?
6,568 Views
Last Modified: 2014-07-21
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
0
Comment
Question by:pippen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 40209045
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
 

Author Comment

by:pippen
ID: 40209084
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
 
LVL 14

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 600 total points
ID: 40209101
Does the sequence "cep_dcs_seq" even exists?!
btw: why do you create the trigger "twice" (CREATE OR REPLACE TRIGGER cep_dcs_trig )?!
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Accepted Solution

by:
sdstuber earned 800 total points
ID: 40209102
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
 

Author Comment

by:pippen
ID: 40209611
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
 
LVL 16

Assisted Solution

by:Wasim Akram Shaik
Wasim Akram Shaik earned 600 total points
ID: 40209658
Is the select retrieving data??.. Check the select statement output..!!!
0
 

Author Comment

by:pippen
ID: 40209660
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
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40209683
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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question