Re: increment-trigger

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 17 Aug 2002 07:02:54 -0700
Message-ID: <ajll2e012fp@drn.newsguy.com>

In article <ajlika$vl4$06$1_at_news.t-online.com>, "Marius says...
>
>Hi there,
>
>I try to do an auto_increment-funtion with a trigger:
>
>I have a sequence:
>Create or replace sequence KI_SEQ increment by1 start with 1 cache 2;
>
>A table:
>Create table KIDS(
>KI_ID NUMBER(5) PRIMARY KEY NOT NULL,
>NAME VARCHAR(200)
>....etc);
>
>And the Trigger
>Create or replace Trigger KI_PK before insert on KIDS for each row
>Begin
>Select KI_SEQ.NEXTVAL into :NEW.KI_ID from DUAL;
>End;
>/
>
>done.
>
>When I start the query:
>Insert into KIDS VALUES ('', 'Anything'.....)
>follows this Warning:
>OCIStmtExecute: ORA-04098: trigger 'MY_OR.KI_PK' is invalid and failed
>re-validation
>and the insert doesnīt happen.
>
>I donīt know Oracle very much, but does anybody knows trigger like this?
>And knows where my error is?
>
>Thx
>Marius
>
>
>
>
>
>

Here is how to "debug" this:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> Create or replace sequence KI_SEQ increment by1 start with 1 cache 2;
Create or replace sequence KI_SEQ increment by1 start with 1 cache 2

                  *

ERROR at line 1:
ORA-00922: missing or invalid option

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> Create table KIDS(   2 KI_ID NUMBER(5) PRIMARY KEY NOT NULL,   3 NAME VARCHAR(200)
  4 );
Create table KIDS(

             *
ERROR at line 1:
ORA-00955: name is already used by an existing object

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> Create or replace Trigger KI_PK before insert on KIDS for each row
  2 Begin
  3 Select KI_SEQ.NEXTVAL into :NEW.KI_ID from DUAL;   4 End;
  5 /

Warning: Trigger created with compilation errors.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> show errors trigger ki_pk Errors for TRIGGER KI_PK:

LINE/COL ERROR

-------- -----------------------------------------------------------------
2/1      PL/SQL: SQL Statement ignored
2/8      PLS-00201: identifier 'KI_SEQ.NEXTVAL' must be declared

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> Create sequence KI_SEQ increment by 1 start with 1 cache 2;

Sequence created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> Create or replace Trigger KI_PK before insert on KIDS for each row
  2 Begin
  3 Select KI_SEQ.NEXTVAL into :NEW.KI_ID from DUAL;   4 End;
  5 /

Trigger created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into kids values ( '', 'anything' );

1 row created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select * from kids   2 /

     KI_ID NAME

---------- ------------------------------
         1 anything

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>

create or replace isn't valid with sequences by1 is missing a space
the trigger is invalid due to the sequence not existing.

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Sat Aug 17 2002 - 09:02:54 CDT