触发器中的错误消息绑定变量错误 [英] Error message bad bind variable in trigger

问题描述

我正在尝试编写一个触发器来在插入之前验证客户/地址是否存在，但我遇到了一些错误.能否就此提出建议?

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?

下表是:
客户(客户 ID、客户名称、DOB)

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;

以下是错误信息:
错误(27,46):PLS-00049:错误的绑定变量NEW.CUSTNAME"
错误(27,60):PLS-00049:错误的绑定变量'NEW.DOB'

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'

推荐答案

触发器定义在 ACCOUNT 表上.您已经发布了 CUSTOMER 表的定义.除非 ACCOUNT 表包含列 CustName 和 DOB，这似乎不太可能，否则您不能引用 :new.CustName 或 :new.DOB——:new 记录用于当前插入到 ACCOUNT 表中的行.

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.

您打算从何处获取 CustName 和 DOB 插入到 Customer 表中?

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

退一步，为什么 Account 表上的触发器首先试图将一行插入到 Customer 表中.这似乎是一个非常糟糕的设计.Account 表中的 CustID 大概是引用 Customer 表的外键.但是，这意味着，如果您将约束声明为可延迟并在每个事务开始时延迟它们，则您只能在触发器中插入父行.触发器通常也无法确定您要填充的 Customer 列的信息，而这些信息是您获得错误的来源.

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.

这篇关于触发器中的错误消息绑定变量错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！