Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2012
    Posts
    2

    Unanswered: Error during trigger creation

    Hi!
    I'm trying t create a table with an autoincrement in one of its columns.
    I'm doing the following:

    CREATE TABLE "OAAM_USER". ( "TRANS-ID" NUMBER(15), "TRANS-TYPE" VARCHAR2(30) NOT NULL , "TRANS_DATETIME" TIMESTAMP(6) NOT NULL , "USER_ID" VARCHAR2(30) NOT NULL ,
    "DEVICE_ID" NUMBER(15), "AMOUNT" NUMBER(16, 2) NOT NULL , "USER_LIMIT" NUMBER(16, 2) NOT NULL , "CERT_STATUS" VARCHAR2(20), PRIMARY KEY ("TRANS-ID") VALIDATE )
    ORGANIZATION INDEX TABLESPACE "BRSADATA" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING;

    CREATE SEQUENCE "OAAM_USER"."MAIN_TABLE_SEQ" NOCYCLE NOORDER CACHE 20 NOMAXVALUE MINVALUE 0 INCREMENT BY 1 START WITH 1;

    create or replace trigger main_table_tr before insert on MAIN_TABLE
    referencing new as new for each row
    begin
    select main_table_seq.nextval
    into :new.trans-id
    from dual;
    end;


    When I run the last statement to create the trigger, the following appears:
    Trigger created with compilation errors

    and when I type show errors I got

    2/1 PL/SQL: SQL Statement ignored
    3/6 PLS-00049: bad bind variable 'NEW.TRANS'
    3/16 PL/SQL: ORA-00923: FROM keyword not found where expected


    Can somebody help?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    The column name "TRANS-ID" contains an invalid character (or at least one that isn't allowed without quoting). Your CREATE TABLE statement works, because you did use double quotes for the column names (and therefor the invalid dash character was accepted). Because of that you now need to always use double quotes when referring to that column.

    Code:
    create or replace trigger main_table_tr before insert on MAIN_TABLE
    referencing new as new for each row
    begin
       select main_table_seq.nextval
         into :new."TRANS-ID"
       from dual;
    end;
    Note that by using double quotes the name is case sensitive as well. So "TRANS-ID" is a different column than "trans-id".

    I would strongly suggest to rename those columns and replace the dash with an underscore (as you did with the other columns). That will save you a lot of trouble in the long run.

    Btw: if you are using Oracle 11, you can directly assign the sequence value in the trigger:

    Code:
    create or replace trigger main_table_tr before insert on MAIN_TABLE
    referencing new as new for each row
    begin
       :new."TRANS-ID" := main_table_seq.nextval;
    end;
    For future posts, please read the online help for formatting code blocks: http://www.dbforums.com/misc.php?do=bbcode
    (Especially the [code] tag, see my code examples to get an idea how it will look like)

  3. #3
    Join Date
    Jun 2012
    Posts
    2
    @shammat,
    I followed your advices and now I have no errors.
    Thank you very much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •