Триггер является недопустимым и неудавшимся приданием вновь юридической силы

Вот код, который я использую для составления таблицы, последовательности и триггера

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

Когда я выполняю код, я получаю следующий вывод

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

Что делает мой триггер недопустимым?

5
задан Raj More 9 August 2010 в 13:31
поделиться