1 Reply Latest reply: Dec 13, 2010 4:30 AM by 823385 RSS

    Trigger Compilation Error

    821250
      Good day all. I have been trying to get this trigger to work for the past three hours but to no avail. I would greatly appreciate some assistance with the two errors generated from the trigger compilation please.

      the code is:

      CREATE TABLE "User"
      (     "userId" NUMBER NOT NULL ENABLE,
           "userName" VARCHAR2(20) NOT NULL ENABLE,
           "password" VARCHAR2(20) NOT NULL ENABLE,
           "fName" VARCHAR2(30) NOT NULL ENABLE,
           "lName" VARCHAR2(30) NOT NULL ENABLE,
           "dob" Date NOT NULL ENABLE,
           "dateCreated" DATE NOT NULL ENABLE,
           "userType" VARCHAR2(20) NOT NULL ENABLE,
           CONSTRAINT "USER_PK" PRIMARY KEY ("userId") ENABLE,
           CONSTRAINT "uSER_FK1" FOREIGN KEY ("userType")
                REFERENCES "UserTypeLookUp" ("userType") ENABLE)
      /
      CREATE SEQUENCE "User_SEQ" MINVALUE 1 MAXVALUE 9999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE
      /
      CREATE OR REPLACE TRIGGER "BI_User"
      before insert on "User"
      for each row
      begin
      select "User_SEQ".nextval into :NEW.userId from dual
      end;
      /
      ALTER TRIGGER "BI_User" ENABLE;

      the error when i compile the trigger is;

      Line Position Text
      2 35 PLS-00049: bad bind variable 'NEW.USERID'
      4 1 PLS-00103: Encountered the symbol "/" when expecting one of the following: begin case declare end exception exit for goto if loop mod null pragma raise return select update while with

      When i try to do and insert on the table i get this error;

      ORA-04098: trigger 'CSR.BI_User' is invalid and failed re-validation
           Error     Unable to process row of table User.
      OK