在 Oracle Express 中创建触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12166046/
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
Creating a trigger in Oracle Express
提问by bread butter
I was trying to do something like auto-increment in Oracle 11g Express and SQL Developer. I know very little about Oracle and I am also new to triggers.
我试图在 Oracle 11g Express 和 SQL Developer 中做一些类似自动增量的事情。我对 Oracle 知之甚少，而且我也是触发器的新手。
I tried running this, but I don't know how to do it properly.
我试过运行这个，但我不知道如何正确地做到这一点。
CREATE TABLE theschema.thetable
(id NUMBER PRIMARY KEY,
name VARCHAR2(30));
CREATE SEQUENCE theschema.test1_sequence
START WITH 1
INCREMENT BY 1;
create or replace trigger insert_nums
before insert on theschema.thetable
for each row
begin
select test1_sequence.nextval into :new.id from dual;
end;
/
When I try to create the trigger, I get a screen which asks me for some "binds". The dialog box has only one check box "null". What does this mean and how do I make a script that works properly?
当我尝试创建触发器时，会出现一个屏幕，要求我提供一些“绑定”。该对话框只有一个复选框“空”。这是什么意思，我如何制作一个可以正常工作的脚本？
Any precautions to take while doing this kind of "auto-increment" ?
在进行这种“自动增量”时要采取什么预防措施？
回答by Michael T
It seems that SQL Developer thinks that you are running a plain DML (data manipulation) script, not a DDL (data definition). It also thinks that :new.idis a bindable variable.
SQL Developer 似乎认为您正在运行一个普通的 DML（数据操作）脚本，而不是一个 DDL（数据定义）。它还认为这:new.id是一个可绑定的变量。
Why this happens, I don't know; I can't reproduce it in Oracle SQL Developer 2.1.
为什么会这样，我不知道；我无法在 Oracle SQL Developer 2.1 中重现它。
Try to open a new SQL worksheet window in the theschemaschema and execute a "whole" script (not a statement) by pressing F5(not F9).
尝试在模式中打开一个新的 SQL 工作表窗口，theschema然后按F5(not F9)执行“整个”脚本（不是语句）。
回答by doki42
This is how I have solved this problem, put "set define off;" before the command:
我就是这样解决这个问题的，把“set define off;” 在命令之前：
set define off;
create or replace trigger [...]
[...]
end;
/
Then highlight both commands and press F9 to run. Or you could run all the commands with F5.
然后突出显示这两个命令并按 F9 运行。或者您可以使用 F5 运行所有命令。
It seems, that if the commands are executed separetly with F9, then the set define off does not take affect.
看来，如果这些命令是用 F9 单独执行的，那么 set define off 不会生效。
回答by VolkanT
For my case, solution was entering "newrow" for 'new' and "oldrow" for 'old' as values for the binds...
就我而言，解决方案是为“新”输入“newrow”，为“旧”输入“oldrow”作为绑定值...
回答by Rod Hittle
I am a novice at this so keep that in mind as I give my answer. I think the issue is that the code
我是这方面的新手，所以在我给出答案时请记住这一点。我认为问题在于代码
create or replace trigger insert_nums
before insert on theschema.thetable
for each row
begin
select test1_sequence.nextval into :new.id from dual;
end;
Is actually a script and not straight forward SQL statement. Hence you have to run the "Run Script". I discovered that when I had a worksheet open in SQL Developer that if I had anywhere in the worksheet the any code for trigger like above then even I just tried to run a statement that SQL Developer would look back in the worksheet and try to run the script. To stop that from happening I had to comment out the code. And If I did want to run the code for the trigger than I had to open a new worksheet, place the code there and do a RUN SCRIPT.
实际上是一个脚本而不是直接的 SQL 语句。因此，您必须运行“运行脚本”。我发现，当我在 SQL Developer 中打开一个工作表时，如果我在工作表中的任何位置都有上述触发器的任何代码，那么即使我只是尝试运行 SQL Developer 会在工作表中回顾并尝试运行的语句脚本。为了阻止这种情况发生，我不得不注释掉代码。如果我确实想运行触发器的代码而不是打开一个新的工作表，请将代码放在那里并执行运行脚本。
