DROP trigger only if it exists (ORACLE)

Question

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**

Solution

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