Re: Using SQL to generate Trigger creation scripts

Hello James

try this :

set feedback off
set heading off
set verify off
set termout off
set pagesize 0
set long 999999
set arraysize 1

column buff fold_after 1
column trigger_body format a135 word_wrapped

spool &trigger_output_file

select
'CREATE OR REPLACE TRIGGER ' || owner || '.' || trigger_name buff , substr(trigger_type,1,((instr(trigger_type,' ',1) -1))) || ' ' || triggering_event || ' ON ' || table_owner || '.' || table_name buff , referencing_names || ' ' || decode(instr(trigger_type,'ROW'), 0 , '', 'FOR ' || substr(trigger_type, instr(trigger_type,' ',1)+1)) buff , decode(when_clause, null,null,'WHEN (' || when_clause || ')') buff , trigger_body buff ,
'.' buff ,
'/' buff
from all_triggers
where owner like upper('&owner')
  and table_name like upper('&table')
  and trigger_name not like 'TLOG$%'
order by owner, table_owner, table_name, trigger_name, trigger_type, triggering_event ;

spool off
exit

Hope this help's

Regards
Olivier

James wrote:

> Does anyone have or know of SQL that I can run off of DBA tables to get
> a trigger creation script.
>
> thank you in advance,
>
> -jef
Received on Wed Aug 19 1998 - 15:50:21 CDT