CODEWITHSUNDEEP
oracle-database
Belitho Xavier
Belitho Xavier

Reputation: 139

Can i disable a trigger inside a trigger in oracle?

I have 2 triggers and two tables. One(trigger) is to insert inside one column in the other table when a row in first table is inserted, the other one prevent insert and update in that column. I want to know if there is a way i can disable the second trigger that prevents the insert/update during the execution of first trigger.

Upvotes: 0

Views: 1174

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

This is how I understood the question. See if it helps.

Sample tables:

SQL> create table test (id number);

Table created.

SQL> create table test_2 (id number);

Table created.

A trigger on test_2 which prevents inserts:

SQL> create or replace trigger trg2
  2    before insert or update on test_2
  3    for each row
  4  begin
  5    raise_application_error(-20000, 'Not allowed');
  6  end;
  7  /

Trigger created.

Does it work?

SQL> insert into test_2 (id) values (1);
insert into test_2 (id) values (1)
            *
ERROR at line 1:
ORA-20000: Not allowed
ORA-06512: at "SCOTT.TRG2", line 2
ORA-04088: error during execution of trigger 'SCOTT.TRG2'

Yes, it works.

Now, a trigger on test which is supposed to a) disable trg2 trigger and b) insert value into test_2. A straightforward code would then be

SQL> create or replace trigger trg1
  2    before insert on test
  3    for each row
  4  begin
  5    execute immediate 'alter trigger trg2 disable';
  6    insert into test_2 (id) values (:new.id);
  7  end;
  8  /

Trigger created.

Let's test it:

SQL> insert into test (id) values (1);
insert into test (id) values (1)
            *
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "SCOTT.TRG1", line 2
ORA-04088: error during execution of trigger 'SCOTT.TRG1'

Aha. Can't COMMIT in a trigger. Where is it? In dynamic SQL's alter trigger - it is a DDL and it implicitly commits. How to "fix" it? Make it (a trigger) an autonomous transaction:

SQL> create or replace trigger trg1
  2    before insert on test
  3    for each row
  4  declare
  5    pragma autonomous_transaction;
  6  begin
  7    execute immediate 'alter trigger trg2 disable';
  8    insert into test_2 (id) values (:new.id);
  9  end;
 10  /

Trigger created.

SQL> insert into test (id) values (1);
insert into test (id) values (1)
            *
ERROR at line 1:
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "SCOTT.TRG1", line 6
ORA-04088: error during execution of trigger 'SCOTT.TRG1'

That's another error; it says that - if we have an autonomous transaction - we have to either commit or roll back. Let's commit (because that's probably what you'd want to do):

SQL> create or replace trigger trg1
  2    before insert on test
  3    for each row
  4  declare
  5    pragma autonomous_transaction;
  6  begin
  7    execute immediate 'alter trigger trg2 disable';
  8    insert into test_2 (id) values (:new.id);
  9    commit;
 10  end;
 11  /

Trigger created.

SQL> insert into test (id) values (100);

1 row created.

SQL> select * From test;

        ID
----------
       100

SQL> select * from test_2;

        ID
----------
       100

SQL>

Right; now it works.

I suggest you re-read comments posted below your question, see this example and choose what to do.

Upvotes: 2

Related Questions