Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Posts
    6

    Unanswered: triggers "bad bind variable " error

    hi...... i was doing a trigger (UpdateCustomer) from a view (CustomerAssignment) below is my code....
    but i keep getting compilation error (bad bind var)...can someone here please tell me what im missing?
    for the trigger im supposed to check if Last_name already exist or not...if it is i should print an error message, if its not i need to update the table
    Thank you so much

    CREATE TRIGGER UpdateCustomer
    INSTEAD OF UPDATE ON CustomerAssignment
    REFERENCING OLD AS old NEW AS new
    FOR EACH ROW
    DECLARE
    rowcount int;
    poc_missing_error EXCEPTION;
    BEGIN
    SELECT Count(*) INTO rowcount
    FROM Customer C
    WHERE C.LAST_NAME =ld.CUSTOMER;
    IF rowcount > 1 THEN
    poc_missing_error EXCEPTION;
    ELSE
    UPDATE CUSTOMER
    SET CUSTOMER.LAST_NAME =:new.CUSTOMER
    WHERE CUSTOMER.LAST_NAME =ld.CUSTOMER;
    END IF;
    EXCEPTION
    WHEN poc_missing_error THEN
    RAISE_APPLICATION_ERROR (-20001, 'NAME EXIST CAN NOT UPDATE IT');
    END;

    CREATE OR REPLACE VIEW CustomerAssignment (LASTNAME, FIRSTNAME, ROOM)AS
    SELECT C.LAST_NAME,C.FIRST_NAME, B.ROOM_ID
    FROM CUSTOMER C , STORE S
    WHERE C.STORE_ID = S. STORE_ID
    ;
    Select * from RoomAssignment;

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: triggers "bad bind variable " error

    Instead of :new.CUSTOMER and :old.CUSTOMER you should write :new.LASTNAME and :old.LASTNAME - i.e. the view's column name, not the table name.

    If customer last_name values must be unique, then create a unique constraint:

    ALTER TABLE customer ADD CONSTRAINT customer_uk UNIQUE (last_name);

    Now your triggers simplifies vastly to:
    PHP Code:
    CREATE TRIGGER UpdateCustomer
    INSTEAD OF UPDATE ON CustomerAssignment
    FOR EACH ROW
    BEGIN
      UPDATE CUSTOMER
      SET CUSTOMER
    .LAST_NAME =:new.CUSTOMER
      WHERE CUSTOMER
    .LAST_NAME =:old.CUSTOMER;
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        RAISE_APPLICATION_ERROR 
    (-20001'NAME EXIST CAN NOT UPDATE IT');
    END

Posting Permissions

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