条件触发

Question

create or replace trigger insert_test_id
before insert on test
where(test.name='Ash')
begin
insert into test(s_no) values('def');
end

我的桌子正在

测试 id 整数 名称 varchar2(200) s_no varchar2(250)

请告诉我此触发器中的错误是什么。我无法找出答案。

Answer 1

快速浏览一下在线文档已经告诉过您条件语法是 WHEN 而不是 WHERE。

您还应该使用 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>

条件也有效...

SQL> insert into t23 values ('Ash')
  2  /

1 row created.

SQL> select name from t23
  2  /

NAM
---
abc
def
Ash

3 rows selected.

SQL>

它甚至适用于多行...

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>

那么问题是什么？这：

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>

当然我在这里作弊，产生了错误。如果测试值和替换值都被硬编码，则可以避免该问题。但如果其中一个是查找，那么就存在递归的风险。

---

如果您实际想要做的是替换输入值而不是插入额外的行，您应该使用 简单的赋值语法由@Lukas发布。

Answer 2

然后试试这个：

CREATE OR REPLACE TRIGGER insert_test_id
BEFORE INSERT ON test
WHEN(new.name='Ash')
FOR EACH ROW
BEGIN
  :new.s_no := 'def';
END;

“FOR EACH ROW”使其成为语句级触发器，针对受插入表影响的每一行执行。那应该摆脱 ora-04077

Answer 3

我认为您不能用这样的递归行为定义触发器。正确的方法是

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;

但是，这只会插入一条记录，而不是两条记录，如果这是您的初衷的话。