SQL PLS-00049 错误的绑定变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22583725/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PLS-00049 BAD BIND VARIABLE
提问by Chuck
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.
我正在尝试设置一个触发器，以便每当 PL_Witness 表更新时，它都会在审计表 PLAUDWIT 表中进行记录。
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?
但是，每次我尝试制作这个触发器时，我都会得到错误的绑定变量，并且我也在尝试制作的其他审计触发器上得到了这个。我的常见问题是什么？
All Help is appreciated!
感谢所有帮助！
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));
. CREATE OR REPLACE TRIGGER TRG_PLAUDWIT
. 创建或替换触发器 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;
结束 TRG_PLAUDWIT;
回答by Alex Poole
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_TYPEcolumn.
您指的是'A'在开头使用 an而没有下划线的绑定变量，例如:OLD.AWitnessID，但您的表格列只是WITNESS_ID. 所以它们不匹配，并产生这个错误。你甚至没有WITNESS_TYPE专栏。
Then in your insertstatements you have the column names in the audit table wrong too. You also set the variable to Updatebut check for UPDATE- remmeber the comparison is case-sensitive for string values.
然后在您的insert语句中，您的审计表中的列名也是错误的。您还将变量设置为Update但检查UPDATE- 请记住，字符串值的比较区分大小写。
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;
/
