Disable trigger in another db(Oracle)

Can I create a procedure that disables a trigger in another database? I mean, can I disable it with a database link? I need it for importing data into a data warehouse

Answer

Yes, you can do that. Here’s how.

In a remote database (called ORCL), I’m creating a table and a trigger:

SQL> create table test (id number);

Table created.

SQL> create or replace trigger trg_test
  2    before insert on test
  3    for each row
  4  begin
  5    null;
  6  end;
  7  /

Trigger created.

Furthermore, in the same (remote) database, I’m creating a procedure which will disable that trigger. It’ll use dynamic SQL as you can’t execute DDL in PL/SQL just like that:

SQL> create or replace procedure p_disable_trg_test as
  2  begin
  3    execute immediate 'alter trigger trg_test disable';
  4  end;
  5  /

Procedure created.

Now, in a local database, I’m creating a database link to the ORCL database:

SQL> create database link dbl_scott_orcl
  2    connect to scott
  3    identified by tiger
  4    using 'orcl';

Database link created.

Does it work?

SQL> select * from dual@dbl_scott_orcl;

D
-
X

Yes, it does. Fine. Now, all you have to do is to call the remote procedure from the local database:

SQL> begin
  2    p_disable_trg_test@dbl_scott_orcl;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL>

Let’s check the remote database’s trigger status:

SQL> select trigger_name, status from user_Triggers where trigger_name = 'TRG_TEST';

TRIGGER_NAME                   STATUS
------------------------------ --------
TRG_TEST                       DISABLED

SQL>

DISABLED, as expected.