Raj More Raj More - 5 months ago 17
SQL Question

Trigger is invalid and failed re-validation

Here is the code that I use to create a table, a sequence and a trigger

DROP TABLE CDR.ExtDL_JobStatus;

--
-- TABLE: CDR.ExtDL_JobStatus
--

CREATE TABLE CDR.ExtDL_JobStatus(
Id NUMBER(38, 0) NOT NULL,
ShortName NUMBER(38, 0) NOT NULL,
Description NUMBER(38, 0) NOT NULL,
CONSTRAINT PK_ExtDL_JobStatus PRIMARY KEY (Id)
)
;



Declare NumOfSequences NUMBER :=0;
Begin
Select COUNT(*)
INTO NumOfSequences
FROM All_Sequences
WHERE 1=1
And upper (Sequence_Owner) = upper ('CDR')
And upper (Sequence_Name) = upper ('ExtDL_JobStatus_Seq');
If NumOfSequences > 0 Then
Execute IMMEDIATE 'DROP SEQUENCE CDR.ExtDL_JobStatus_Seq';
End If;
End;
/
CREATE SEQUENCE CDR.ExtDL_JobStatus_Seq
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOMINVALUE
;
/

Declare NumOfTriggers NUMBER :=0;
Begin
SELECT COUNT(*)
INTO NumOfTriggers
FROM All_Triggers
WHERE 1=1
And upper (Owner) = upper ('CDR')
And upper (Trigger_Name) = upper ('ExtDL_JobStatus_SeqTrg');
If NumOfTriggers > 0 Then
Execute IMMEDIATE 'DROP SEQUENCE CDR.ExtDL_JobStatus_SeqTrg';
End If;
End;
/
CREATE TRIGGER CDR.ExtDL_JobStatus_SeqTrg
BEFORE INSERT
ON CDR.ExtDL_JobStatus
FOR EACH ROW
WHEN (new.Id IS NULL)
BEGIN
SELECT ExtDL_JobStatus_SeqTrg.nextval into :new.Id from dual;
END;


/
INSERT INTO ExtDL_JobStatus (Id, ShortName, Description) Values (0, 'Success', 'Fail')
/
SELECT * FROM ExtDL_JobStatus


When I execute the code, I get the following output

DROP TABLE CDR.ExtDL_JobStatus succeeded.
CREATE TABLE succeeded.
anonymous block completed
CREATE SEQUENCE succeeded.
anonymous block completed
Warning: execution completed with warning
TRIGGER CDR.ExtDL_JobStatus_SeqTrg Compiled.

Error starting at line 62 in command:
INSERT INTO ExtDL_JobStatus (Id, ShortName, Description) Values (0, 'Success', 'Fail')
Error at Command Line:62 Column:12
Error report:
SQL Error: ORA-04098: trigger 'CDR.EXTDL_JOBSTATUS_SEQTRG' is invalid and failed re-validation
04098. 00000 - "trigger '%s.%s' is invalid and failed re-validation"
*Cause: A trigger was attempted to be retrieved for execution and was
found to be invalid. This also means that compilation/authorization
failed for the trigger.
*Action: Options are to resolve the compilation/authorization errors,
disable the trigger, or drop the trigger.
ID SHORTNAME DESCRIPTION
---------------------- ---------------------- ----------------------

0 rows selected


What makes my trigger invalid?

Answer

Warning: execution completed with warning TRIGGER CDR.ExtDL_JobStatus_SeqTrg Compiled.

This is where your trigger compilation failed.

sql> CREATE TRIGGER ExtDL_JobStatus_SeqTrg
  2  BEFORE INSERT
  3  ON ExtDL_JobStatus
  4      FOR EACH ROW
  5      WHEN (new.Id IS NULL)
  6      BEGIN
  7          SELECT ExtDL_JobStatus_SeqTrg.nextval into :new.Id from dual;
  8      END;
  9  /

Warning: Trigger created with compilation errors.

sql> show errors;
Errors for TRIGGER EXTDL_JOBSTATUS_SEQTRG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/9      PL/SQL: SQL Statement ignored
2/16     PL/SQL: ORA-02289: sequence does not exist

The problem is because you are using ExtDL_JobStatus_SeqTrg in your code and the sequence you created is ExtDL_JobStatus_Seq.

Also, if you are trying to run a script like this for creating (compiling) the objects, I would suggest you add the following clause after each trigger/procedure/function creatin statement.

SHOW ERRORS;

If your statement succceds, that will just produce no errors. If there are any erros, you'll have a detailed description of the errors instead of having to execute the script again.

Comments