条件触发器

|| 
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） 请告诉我该触发器有什么错误。我找不到。     
2020-05-27 4 条评论
分享

没有找到相关结果

    已邀请:

    羔磺

            快速浏览一下在线文档，您会发现条件语法是“何时不存在”。 您还应该使用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发布的简单赋值语法。     
    2020-05-27 0 0
    分享

    抵浮细

            然后尝试这个： 
    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     
    2020-05-27 0 0
    分享

    骚瓤

            我不认为您可以用这种递归行为定义触发器。正确的方法是 
    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;
    但是，这只是插入一条记录，而不是两条记录（如果那是您的原始意图）。     
    2020-05-27 0 0
    分享
    为什么被折叠? 0 个回复被折叠

    要回复问题请先登录或注册

    或代码 OrCode.com 备案号：粤ICP备15020848号-1
    Escape time: 0.039035081863403