How to drop (remove, delete) all triggers within given DB.
Problem is that application requires DB upgrade and does not proceed triggers (suport, drop, create) after upgrade, application upgrade fails.
Add a comment
|
3 Answers
This will generate the command how to drop all triggers in current schema:
select 'drop trigger ' || trigger_name || ';' stmt from user_triggers;
1 Comment
cdaiga
This just helped me!
You can create a script for dropping triggers by using the Oracle system tables, like this:
select 'drop trigger ' || owner || '.' || trigger_name || ';' from all_triggers
Note that there are 3 views containing triggers:
- all_triggers = all the triggers you have permission to know about (regardless of which schema they belong to)
- user_triggers = the triggers that belong to your own schema
- dba_triggers = for DBA's
Comments
First Google hit for search query: Drop all triggers - Oracle
BEGIN
FOR i in (select trigger_name,owner
from dba_triggers
where trigger_name like '%_BI%' and owner = 'myTesting' ) LOOP
EXECUTE IMMEDIATE 'DROP TRIGGER '||i.owner||'.'||i.trigger_name;
END LOOP;
END;
If you really want to drop all the triggers in the database,
BEGIN
FOR i in (select trigger_name,owner
from dba_triggers ) LOOP
EXECUTE IMMEDIATE 'DROP TRIGGER '||i.owner||'.'||i.trigger_name;
END LOOP;
END;
3 Comments
Allan
The second block is a really bad idea, since it would attempt to drop
SYS and SYSTEM triggers as well as any user-created triggers. Either the block would fail due to a lack of permissions, or the database would be left in a broken state.
saritonin
Hey, he said all the triggers... :-) Nothing another where clause couldn't fix, though.
where owner not in ('SYS','SYSTEM')
trask
Now this page is the top hit for 'drop all triggers oracle' :-)