Oracle 9i auto increment trigger/sequence doesnt work

Question

The following SQL, upon being executed on an Oracle 9i server, yields the the error " ORA-04098: trigger 'DBO.WTF_TRIGGER' is invalid and failed re-validation".

DROP TABLE "DBO".WTF;

CREATE TABLE "DBO".WTF
(id NUMBER PRIMARY KEY,
name VARCHAR2(30));

CREATE SEQUENCE "DBO".WTF_sequence
START WITH 1
INCREMENT BY 1;

CREATE OR REPLACE TRIGGER "DBO".WTF_trigger
BEFORE INSERT
ON "DBO".WTF
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT "DBO".WTF_sequence.nextval INTO :NEW.id FROM dual;
END;

INSERT INTO "DBO".WTF (name) VALUES ('asd');

Any ideas?

If you run the sql*plus command SHOW ERRORS it will tell you what syntax error or other problem is preventing your trigger from compiling. — APC, Sep 01 '11 at 17:46

score 3 · Accepted Answer · answered Sep 01 '11 at 17:58

As APC points out, it would be helpful to do a SHOW ERRORS in SQL*Plus to print out the errors. The code you posted works perfectly for me if I create a DBO user with appropriate privileges.

SQL> conn / as sysdba
Connected.
SQL> create user dbo identified by dbo;

User created.

SQL> grant connect, resource, unlimited tablespace to dbo;

Grant succeeded.

SQL> conn dbo/dbo
Connected.
SQL> DROP TABLE "DBO".WTF;
DROP TABLE "DBO".WTF
                 *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> CREATE TABLE "DBO".WTF
  2  (id NUMBER PRIMARY KEY,
  3  name VARCHAR2(30));

Table created.

SQL>
SQL> CREATE SEQUENCE "DBO".WTF_sequence
  2  START WITH 1
  3  INCREMENT BY 1;

Sequence created.

SQL>
SQL> CREATE OR REPLACE TRIGGER "DBO".WTF_trigger
  2  BEFORE INSERT
  3  ON "DBO".WTF
  4  REFERENCING NEW AS NEW
  5  FOR EACH ROW
  6  BEGIN
  7  SELECT "DBO".WTF_sequence.nextval INTO :NEW.id FROM dual;
  8  END;
  9  /

Trigger created.

SQL> INSERT INTO "DBO".WTF (name) VALUES ('asd');

1 row created.

SQL> select * from wtf;

        ID NAME
---------- ------------------------------
         1 asd

Oracle 9i auto increment trigger/sequence doesnt work

1 Answers1