Error message bad bind variable in trigger

Question

I am trying to write a trigger to validate if the customer/address exist before inserting but i encounter some error. Able to advise on this?

Below is the table:
Customer (CustID,CustName,DOB)

CREATE OR REPLACE TRIGGER CREATEACCOUNT
  BEFORE INSERT ON ACCOUNT
  FOR EACH ROW 
DECLARE
  newCustID     varchar(10);
  newPostCode   int;
  newStreet     char;
  newAccType    varchar(15);
  newAccStatus  char(9);
  newAccBalance int;
  varRowCount   int;
BEGIN

  newCustID := :new.CustID;

  SELECT COUNT(*) 
    INTO varRowCount
    FROM Customer
   WHERE CustID = newCustID;

  IF (varRowCount > 0) THEN 
    RETURN;
  END IF;

  IF (varRowCount = 0) THEN
    BEGIN
      INSERT INTO CUSTOMER VALUES (newCustID,:new.CustName,:new.DOB);
    END;
  END IF;
END;

Below is the error message:
Error(27,46): PLS-00049: bad bind variable 'NEW.CUSTNAME'
Error(27,60): PLS-00049: bad bind variable 'NEW.DOB'

Justin Cave · Accepted Answer · 2012-11-16 04:15:26Z

1

The trigger is defined on the ACCOUNT table. You've posted the definition of the CUSTOMER table. Unless the ACCOUNT table has columns CustName and DOB, which seems highly unlikely, you can't refer to :new.CustName or :new.DOB-- the :new record is for the row that is currently being inserted into the ACCOUNT table.

Where do you intend to get the CustName and DOB to insert into the Customer table?

Taking a step back, why is a trigger on the Account table trying to insert a row into the Customer table in the first place. That seems like an exceptionally poor design. The CustID in the Account table would presumably be a foreign key that references the Customer table. That would mean, though, that you could only insert the parent row in a trigger if you declare the constraints as deferable and defer them at the beginning of every transaction. The trigger would also generally have no way of determining the information for the Customer columns that you want to populate which is the source of the error you're getting.

answered Nov 16, 2012 at 4:15

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

FirstTimer Over a year ago

What would be a better solution to validate if customer/address exist in the database before creating an account? I need it to be done w/o using any application

Collectives™ on Stack Overflow

Error message bad bind variable in trigger

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related