Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2011
    Posts
    2

    Unanswered: Trigger with select ->PLS-00103

    Hi all,

    novice here. Have the following simple code:
    create or replace trigger aifer_deposition
    after insert on deposition
    for each row
    declare
    v_balance account.balance%type;
    begin
    select balance
    into v_balance
    from account
    where acc_id=:new.acc_id
    and date_time=:new.date_time;
    if (v_balance<>:new.amount) then
    raise application_error(-20000,'Balance problem');
    end if;
    end;

    that generates the following error:
    0/24 PLS-00103: Encountered the symbol "(" when expecting one of the following:

    . ;

    10/50 PLS-00103: Encountered the symbol ";" when expecting one of the following:

    , * & - + / at mod remainder rem <an identifier>
    <a double-quoted delimited-identifier> <an exponent (**)> as
    from into || multiset bulk year day

    I know I am missing something here, but what is it?

    Thanks in advance

    BR,
    tolis

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post DDL for both deposition & account
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by tolis1975 View Post
    I know I am missing something here, but what is it?
    Underscore after RAISE on line 10, as the error message locates:
    Code:
    raise application_error(-20000,'Balance problem');
    There may be other errors though; I will let your Oracle compiler to check.

  4. #4
    Join Date
    Oct 2011
    Posts
    2
    Quote Originally Posted by anacedent View Post
    post DDL for both deposition & account
    Nothing special:
    CREATE TABLE deposition
    (
    DEP_ID NUMBER (9) NOT NULL ,
    CUST_ID VARCHAR2 (11) NOT NULL ,
    ACC_ID NUMBER (8) NOT NULL ,
    AMOUNT NUMBER (10,2) ,
    DATE_TIME DATE NOT NULL
    );

    ALTER TABLE deposition
    ADD CONSTRAINT deposition_PK PRIMARY KEY ( DEP_ID ) ;

    CREATE TABLE account
    (
    ACC_ID NUMBER (8) NOT NULL ,
    ACCTY_ID NUMBER (6) NOT NULL ,
    DATE_TIME DATE NOT NULL ,
    BALANCE NUMBER (10,2)
    );

    ALTER TABLE account
    ADD CONSTRAINT account_PK PRIMARY KEY ( ACC_ID ) ;

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SQL> create or replace trigger aifer_deposition
    after insert on deposition
    for each row
    declare
    v_balance account.balance%type;
    begin
    select balance
    into v_balance
    from account
    where acc_id=:new.acc_id
    and date_time=:new.date_time;
    if (v_balance<>:new.amount) then
    raise_application_error(-20000,'Balance problem');
    end if;
    end; 2 3 4 5 6 7 8 9 10 11 12 13 14 15
    16 /

    Trigger created.

    SQL>
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Tags for this Thread

Posting Permissions

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