oracle 条件触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6173556/
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
Conditional Trigger
提问by Durga Dutt
create or replace trigger insert_test_id
before insert on test
where(test.name='Ash')
begin
insert into test(s_no) values('def');
end
my table is
我的桌子是
test id integer name varchar2(200) s_no varchar2(250)
测试 ID 整数名称 varchar2(200) s_no varchar2(250)
please tell me that what is the error in this trigger. I am not able to find out.
请告诉我这个触发器中的错误是什么。我不知道。
回答by APC
A quick glance at the online documentationwould have told you that the conditional syntax is WHEN not WHERE.
快速浏览一下在线文档会告诉您条件语法是 WHEN not WHERE。
You should also reference the column using the NEW keyword rather than the table name. And as Gary rightly points out, we can only apply the conditional clause for ROW LEVEL triggers:
您还应该使用 NEW 关键字而不是表名来引用列。正如 Gary 正确指出的那样，我们只能对 ROW LEVEL 触发器应用条件子句：
SQL> create or replace trigger insert_test_id
2 before insert on t23
3 for each row
4 when (new.name='Ash')
5 begin
6 insert into t23(name) values('def');
7 end;
8 /
Trigger created.
SQL> insert into t23 values ('abc')
2 /
1 row created.
SQL> select name from t23
2 /
NAM
---
abc
1 rows selected.
SQL>
The condition works too...
条件也行...
SQL> insert into t23 values ('Ash')
2 /
1 row created.
SQL> select name from t23
2 /
NAM
---
abc
def
Ash
3 rows selected.
SQL>
It even works for multiple rows....
它甚至适用于多行....
SQL> insert into t23
2 select txt from t42
3 /
4 rows created.
SQL> select name from t23
2 /
NAM
---
abc
def
Ash
XXX
ZZZ
ABC
DEF
7 rows selected.
SQL>
So what's the problem? This:
所以有什么问题？这个：
SQL> create or replace trigger insert_test_id
2 before insert on t23
3 for each row
4 when (new.name='def')
5 begin
6 insert into t23(name) values('def');
7 end;
8 /
Trigger created.
SQL> insert into t23 values ('def')
2 /
insert into t23 values ('def')
*
ERROR at line 1:
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-06512: at "APC.INSERT_TEST_ID", line 2
ORA-04088: error during execution of trigger 'APC.INSERT_TEST_ID'
ORA-06512: at "APC.INSERT_TEST_ID", line 2
ORA-04088: error during execution of trigger 'APC.INSERT_TEST_ID'
ORA-06512: at "APC.INSERT_TEST_ID", line 2
ORA-04088: error during execution of trigger 'APC.INSERT_TEST_ID'
ORA-06512: at "APC.INSERT_TEST_ID", line 2
ORA-04088: error during execution of trigger 'APC.INSERT_TEST_ID'
ORA-06512: at "APC.INSERT_TEST_ID", line 2
ORA-04088: error during execution of trigger 'APC.INSERT_TEST_ID'
ORA-06512: at "APC.INSERT_TEST_ID", line 2
ORA-04088: error during execution of trigger 'APC.INSERT_TEST_ID'
ORA-06512: at "APC.INSERT_TEST_ID", line 2
ORA-04088: error during execution of trigger 'APC.INSERT_TEST_ID'
ORA-06512: at "APC.INSERT_TEST_ID", line 2
ORA-04088: error during execution of trigger 'APC.INSERT_TEST_ID'
ORA-06512: at "APC.INSERT_TEST_ID", line 2
ORA-04088: error during execution of trigger
SQL>
Of course I have cheated here, to generate the error. If both the test value and the substituted value are hard-coded the problem can be avoided. But if either is a lookup, then the risk of recursion is there.
当然，我在这里作弊，以产生错误。如果测试值和替换值都是硬编码的，则可以避免该问题。但如果其中一个是查找，则存在递归风险。
If what you actually want to do is replace an input value rather insert an additional row you should use the simple assignment syntax posted by @Lukas.
回答by DCookie
Try this one then:
然后试试这个：
CREATE OR REPLACE TRIGGER insert_test_id
BEFORE INSERT ON test
WHEN(new.name='Ash')
FOR EACH ROW
BEGIN
:new.s_no := 'def';
END;
The "FOR EACH ROW" makes it a statement level trigger, executed for each row affected by the insert into the table. That should get rid of the ora-04077
“FOR EACH ROW”使其成为语句级触发器，为受表插入影响的每一行执行。那应该摆脱 ora-04077
回答by Lukas Eder
I don't think you can define triggers with recursive behaviour like this. The correct way to do it is
我不认为你可以用这样的递归行为来定义触发器。正确的做法是
create or replace trigger insert_test_id
before insert on test
-- note: it is "when", not "where"
when(test.name='Ash')
begin
-- this is how you override a field from within the trigger
:new.s_no := 'def';
end;
However, this will only insert one record, not two, if that was your original intent.
但是，如果这是您的初衷，这只会插入一条记录，而不是两条记录。
