PLS-00049坏绑定变量 [英] PLS-00049 BAD BIND VARIABLE

问题描述

我正在尝试设置一个触发器，以便每当更新PL_Witness表时，它都会在作为审计表的PLAUDWIT表中对此进行记录.

I'm trying to set up a trigger so that whenever the PL_Witness table is updated, it makes a record of this in the PLAUDWIT table which is an auditing table.

但是，每一次我尝试制作此触发器时，都会得到错误的绑定变量，而我也在尝试中的其他审计触发器上得到此变量.我的常见问题是什么?

However, every single time I try to make this trigger I get bad bind variable, and I am getting this on other audit triggers I am attempting to make too. What is my common issue?

感谢所有帮助！

CREATE TABLE  "PL_WITNESS" 
   (    "WITNESS_ID" NUMBER(*,0) NOT NULL ENABLE, 
    "WITNESS_NAME" VARCHAR2(30) NOT NULL ENABLE, 
    "WITNESS_ADDRESS" VARCHAR2(100), 
    "FK1_WITNESS_TYPE_ID" NUMBER(*,0) NOT NULL ENABLE, 
     CONSTRAINT "PK_WITNESS" PRIMARY KEY ("WITNESS_ID") ENABLE
   )
/
ALTER TABLE  "PL_WITNESS" ADD CONSTRAINT "FK1_WITNESS_WTYPE" FOREIGN KEY         ("FK1_WITNESS_TYPE_ID")
      REFERENCES  "PL_WITNESS_TYPE" ("WITNESS_TYPE_ID") ENABLE
/

.

DROP TABLE PLAUDWIT
CREATE TABLE PLAUDWIT (
AUD_AWitnessID NUMBER,
AUD_AWitnessType NUMBER, 
AUDIT_USER varchar2(50),
AUDIT_DATE DATE,
AUDIT_ACTION varchar2(10));

. 创建或替换触发器TRG_PLAUDWIT

. CREATE OR REPLACE TRIGGER TRG_PLAUDWIT

AFTER INSERT OR DELETE OR UPDATE ON PL_WITNESS
FOR EACH ROW
DECLARE
v_trigger_task varchar2(10);

BEGIN
IF UPDATING
  THEN
    v_trigger_task := 'Update';
ELSIF DELETING
THEN
v_trigger_task := 'DELETE';
ELSIF INSERTING
THEN
v_trigger_task := 'INSERT';
ELSE
v_trigger_task := NULL;
END IF;

IF v_trigger_task IN ('DELETE','UPDATE') THEN
INSERT INTO PLAUDWIT (AWitnessID, AWitnessType, AUDIT_USER, AUDIT_DATE, AUDIT_ACTION)
VALUES
(:OLD.AWitnessID, :OLD.AWitnessType, UPPER(v('APP USER')), SYSDATE, v_trigger_task);
ELSE

INSERT INTO PLAUDWIT (AWitnessID, AWitnessType, AUDIT_USER, AUDIT_DATE, AUDIT_ACTION)
VALUES
(:NEW.AWitnessID, :NEW.AWitnessType, UPPER(v('APP USER')), SYSDATE, v_trigger_task);
END IF;

END TRG_PLAUDWIT;

END TRG_PLAUDWIT;

推荐答案

您指的是在开头使用'A'且没有下划线的绑定变音符，例如:OLD.AWitnessID，但是您的表列只是WITNESS_ID .因此它们不匹配，并产生此错误.您甚至没有WITNESS_TYPE列.

You're referring to bind varibales with an 'A' at the start and no underscore, like :OLD.AWitnessID, but your table column is just WITNESS_ID. So they don't match, and generate this error. You don't even have a WITNESS_TYPE column.

然后，在您的insert语句中，审计表中的列名也将错误.您还可以将变量设置为Update，但要检查UPDATE-注意，比较字符串值时区分大小写.

Then in your insert statements you have the column names in the audit table wrong too. You also set the variable to Update but check for UPDATE - remmeber the comparison is case-sensitive for string values.

这将与您的架构一起编译:

This compiles with your schema:

CREATE OR REPLACE TRIGGER TRG_PLAUDWIT
AFTER INSERT OR DELETE OR UPDATE ON PL_WITNESS
FOR EACH ROW
DECLARE
  v_trigger_task varchar2(10);
BEGIN
  IF UPDATING THEN
    v_trigger_task := 'UPDATE';
  ELSIF DELETING THEN
    v_trigger_task := 'DELETE';
  ELSIF INSERTING THEN
    v_trigger_task := 'INSERT';
  ELSE
    v_trigger_task := NULL;
  END IF;

  IF v_trigger_task IN ('DELETE','UPDATE') THEN
    INSERT INTO PLAUDWIT (AUD_AWitnessID, AUD_AWitnessType, AUDIT_USER,
      AUDIT_DATE, AUDIT_ACTION)
    VALUES (:OLD.Witness_ID, :OLD.FK1_WITNESS_TYPE_ID, UPPER(v('APP USER')),
      SYSDATE, v_trigger_task);
  ELSE    
    INSERT INTO PLAUDWIT (AUD_AWitnessID, AUD_AWitnessType, AUDIT_USER,
      AUDIT_DATE, AUDIT_ACTION)
    VALUES (:NEW.Witness_ID, :NEW.FK1_WITNESS_TYPE_ID, UPPER(v('APP USER')),
      SYSDATE, v_trigger_task);
  END IF;
END TRG_PLAUDWIT;
/

SQL小提琴未显示任何编译错误.

这篇关于PLS-00049坏绑定变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！