Geek asks and answers

Conditional trigger

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

test integer id name varchar2 (200) s_no varchar2 (250)

please tell me that this is a mistake in this trigger. I can’t find out.

+3
source share
3 answers

A quick look at the online documentation would tell you that the conditional syntax is WHEN NOT WHERE.

You should also reference the column using the NEW keyword, not the table name. And, as Gary rightly points out, we can only apply a conditional sentence to ROW LEVEL triggers:

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 works even for multiple lines ....

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? It:

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.

+5

:

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

+3

I don’t think you can define triggers with such recursive behavior. The right way to do this 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 entry, not two, if that was your original intention.

+1
source

More articles:


All Articles