Oracle 创建触发器错误（错误的绑定变量）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14993980/
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
Oracle create trigger error (bad bind variable)
提问by DCookie
I at trying to create trigger with the following code.
我尝试使用以下代码创建触发器。
CREATE OR REPLACE TRIGGER MYTABLE_TRG
BEFORE INSERT ON MYTABLE
FOR EACH ROW
BEGIN
select MYTABLE_SEQ.nextval into :new.id from dual;
END;
I am getting error
我收到错误
Error(2,52): PLS-00049: bad bind variable 'NEW.ID'
Any ideas? Thanks.
有任何想法吗？谢谢。
回答by DCookie
It seems like the error code is telling you there's no such column ID in your table...
似乎错误代码告诉您表中没有这样的列 ID...
回答by JoshL
Somehow your environment is treating your code as SQL instead of a DDL statement. This works for me (running in sqlplus.exe from a command prompt):
不知何故，您的环境将您的代码视为 SQL 而不是 DDL 语句。这对我有用（从命令提示符在 sqlplus.exe 中运行）：
SQL> create sequence mytable_seq;
Sequence created.
SQL> create table mytable (id number);
Table created.
SQL> CREATE OR REPLACE TRIGGER MYTABLE_TRG
2 BEFORE INSERT ON MYTABLE
3 FOR EACH ROW
4 BEGIN
5 select MYTABLE_SEQ.nextval into :new.id from dual;
6 END;
7 /
Trigger created.
Note the trailing "/" - this might be important in the application you are compiling this with.
请注意尾随的“/” - 这在您编译它的应用程序中可能很重要。
回答by Yordan Georgiev
if one would use proper naming convention the spotting of this type of errors would be much easier ( where proper means using pre- and postfixes ) for generic object names hinting about their purpose better i.e. something like this would have spotted the correctanswer
如果人们使用正确的命名约定，那么对于通用对象名称，可以更容易地发现此类错误（其中正确意味着使用前缀和后缀），以更好地暗示其目的，即这样的事情会发现正确的答案
--START -- CREATE A SEQUENCE
/*
create table "TBL_NAME" (
"TBL_NAME_ID" number(19,0) NOT NULL
, ...
*/
--------------------------------------------------------
-- drop the sequence if it exists
-- select * from user_sequences ;
--------------------------------------------------------
declare
c int;
begin
select count(*) into c from user_sequences
where SEQUENCE_NAME = upper('SEQ_TBL_NAME');
if c = 1 then
execute immediate 'DROP SEQUENCE SEQ_TBL_NAME';
end if;
end;
/
CREATE SEQUENCE "SEQ_TBL_NAME"
MINVALUE 1 MAXVALUE 999999999999999999999999999
INCREMENT BY 1 START WITH 1
CACHE 20 NOORDER NOCYCLE ;
-- CREATE
CREATE OR REPLACE TRIGGER "TRG_TBL_NAME"
BEFORE INSERT
ON "TBL_NAME"
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN
tmpVar := 1 ;
SELECT SEQ_TBL_NAME.NEXTVAL INTO tmpVar FROM dual;
:NEW.TBL_NAME_ID := tmpVar;
END TRG_TBL_NAME;
/
ALTER TRIGGER "TRG_TBL_NAME" ENABLE;
-- STOP -- CREATE THE TRIGGER
