3

I want to drop a existing trigger in ORACLE.

I do know the drop query for the trigger in oracle. But wanted to know that how can I check if that trigger is already exists in Oracle DB.

DROP query:

DROP TRIGGER **TRIGGER_NAME**
I Love Stackoverflow
  • 6,738
  • 20
  • 97
  • 216
  • what is the reason for that?cant you just execute the query, if it doesnt exist it will throw an error.if it does, it will be dropped. – brenners1302 Dec 08 '15 at 08:48
  • You can query `[user|all|dba]_triggers` depending on your privileges, whether you are logged in as the owner of the trigger, etc. Or you could simply drop the trigger and catch the exception. – Justin Cave Dec 08 '15 at 08:49
  • I'm a **delphi** developer and I'm creating this **DROP** query from code. So it is the reason I'm checking it with **oracle query**. – I Love Stackoverflow Dec 08 '15 at 08:56
  • @brenners1302: I don't have **ORACLE DB** installed on my machine. So forgive me for this lame question. I wanted to know that what if from code side I execute the query {DROP TRIGGER TRIGGER_NAME} and it throw error without handling any exception? All what I need to do is drop the trigger anyways. Do I need to add exception for that? – I Love Stackoverflow Dec 08 '15 at 09:03

2 Answers2

9

You need a PL/SQL block with dynamic SQL for this:

-- drop the trigger if it exists
declare 
  l_count integer;
begin

  select count(*)
    into l_count
  from user_triggers
  where trigger_name = 'TRIGGER_NAME';

  if l_count > 0 then 
     execute immediate 'drop trigger trigger_name';
  end if;

end;
/


-- now create the trigger    
create trigger trigger_name
   ..
begin
end;
/

Note that (unquoted) identifiers are stored in upper case in the Oracle system catalogs. So make sure you use trigger_name = 'TRIGGER_NAME', not trigger_name = 'trigger_name' in the PL/SQL check

-1

You have two option:

1) Check if trigger exists

SELECT * 
  FROM user_triggers
 WHERE trigger_name = '...'

2) DROP trigger and check for EXCEPTION

hinotf
  • 1,138
  • 1
  • 12
  • 22
  • I don't have ORACLE DB installed on my machine. So forgive me for this lame question. I wanted to know that what if from code side I execute the query {DROP TRIGGER TRIGGER_NAME} and it throw error without handling any exception? All what I need to do is drop the trigger anyways. Do I need to add exception for that? – I Love Stackoverflow Dec 08 '15 at 09:04
  • If trigger do not exists and you drop it, there an exception. So you just need obfuscate exception, cause 'drop trigger anyway': create or replace trigger my_trg before insert on tmp for each row begin null; end; BEGIN -- trigger still exists execute immediate 'DROP TRIGGER my_trg'; -- no more trigger, exception raised execute immediate 'DROP TRIGGER my_trg'; EXCEPTION WHEN OTHERS THEN NULL; END; – hinotf Dec 08 '15 at 09:12