Solved

Drop all trigger in Oracle database

Posted on 2006-07-12
4
1,369 Views
Last Modified: 2008-01-09
Hi, anyone can provide met he script to drop all trigger in oracle database ? TQ
0
Comment
Question by:netcool
4 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 100 total points
ID: 17097239
run this query to generate the script for dropping all triggers for a given schema
select 'drop trigger ' || t.owner || '.' || t.trigger_name || ';' from dba_triggers where t.owner = 'schema_name';
0
 
LVL 47

Expert Comment

by:schwertner
ID: 17097263
use the view  dba_triggers.
it is bad idea to drop ALL triggers.
Try to drop the triggers in an schema.

the idea is to define a cursor

select owner, triggername from dba_triggers where owner = 'SCOTT';

After that in a loop create a string

v_str := 'DROP TRIGGER ' || cursor_name.owner || '.' || cursor_name.triggername
execute immediate v_str;

I think you will learn many things if you try to do this.
But be carefull, do it on experimental instance, not on production.
0
 
LVL 3

Expert Comment

by:haidersyed
ID: 17098172

--To drop all triggers other then sys and system schema

begin
for i in (select trigger_name,owner from dba_triggers where owner not in ('SYS','SYSTEM')) LOOP
execute immediate 'DROP TRIGGER '||i.owner||'.'||i.trigger_name;
END LOOP;
END;
0
 
LVL 6

Expert Comment

by:JJSmith
ID: 17112347


mmmmnnnnn..........

should someone who does not know how to identify/drop triggers in a database;  be dropping all triggers in a database?

normal schemas' can own system triggers!!





0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now