Monday, November 17, 2008

How to drop all triggers existing in a Oracle schema?

Triggers that are created or local with in a schema are listed in “USER_TRIGGERS” tables.
So by executing a sql statement that retrieves all triggers names with in a schema is help full to drop them name by name.

Following sql statement helps out to give a result set that has sql queries to drop each trigger name by name.


select 'drop trigger “'|| trigger_name||'”;' from user_triggers;

So I can spool the result set of this query to an sql file that can be again runned to drop the triggers.

I write a batch that does these 2 tasks
Batch File:-

@echo off
set /p schema=Please enter your TNSname:
set /p username=Please enter your Schema Username:
set /p password=Please enter your Schema Password:
sqlplus %username%/%password%@%schema% @Script1.sql
sqlplus %username%/%password%@%schema% @Script2.sql

Scrip1.sql consists the above mentioned sql query which spools the result set to script2.sql;

So immediately after script1.sql is done then script2.sql is said to be runned.


set heading off
set linesize 1500
set pagesize 1000;
set feedback off
spool Script2.sql
select 'drop trigger “'|| trigger_name||'”;' from user_triggers;
select ‘commit;’ from dual;
select ‘exit;’ from dual;
spool off

No comments:

Post a Comment