Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2012
    Posts
    1

    Wink Unanswered: before insert trigger- doesn't compile

    hi there,

    does anyone know why the following trigger doesnt compile?
    the error i get is pls-00103 and it says something about the symbol "=" .

    CREATE OR REPLACE TRIGGER GAL_TRIGGER
    BEFORE INSERT ON USERS
    FOR EACH ROW
    BEGIN
    DECLARE temp NUMBER;
    temp= SELECT MAX(COUNTER) FROM users;
    :new.COUNTER := temp+1;
    END;


    the purpose of this trigger is to number every record that goes in by taking the max record number and simply incrementing it by 1.

    thanx a lot

  2. #2
    Join Date
    Sep 2012
    Posts
    18

    try this

    CREATE OR REPLACE TRIGGER GAL_TRIGGER
    BEFORE INSERT ON USERS
    FOR EACH ROW
    BEGIN
    DECLARE
    temp NUMBER = 0 ;
    SELECT MAX(COUNTER) into temp FROM users;
    :new.COUNTER := temp+1;
    END;



    try this
    MUHAMMAD ARSALAN

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You have so many errors I wont attempt to fix it. You can't select from a table that you have the trigger on. This will cause a mutating table error. The declare statement is not used in a trigger statement. For counter, which I assume is the primary key, use a sequence then your trigger would look like.

    Code:
    CREATE OR REPLACE TRIGGER GAL_TRIGGER
    BEFORE INSERT  ON USERS
    FOR EACH ROW
    BEGIN
    select users_seq.nextval
    into :new.counter
    from dual;
    END;
    /
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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