Re: Reconstruct trigger from source - How?

From: Chris Hamilton <chamilto_at_sratech.com>
Date: 1996/01/18
Message-ID: <4dlp7e$joa_at_news.cais.com>#1/1

badri_at_cc.gatech.edu (badri) wrote:

> Can anyone out there post a script to spool the trigger
>creation script from the way it is stored in user_triggers or any
>other table? I tried, but the long format gets me all the time and
>I end up getting a messy output.
 

>-Badri

Badri:

Here is a script I wrote to do that...

You can get more stuff at ftp.sratech.com, in the file /pub/oracle/datadict.zip.

Hope this helps.

---

rem *******************************************************************
rem Name:     d_trgddl.sql
rem Author:   Chris Hamilton, SRA Technologies, Inc.
rem Date:     15-Mar-94.  Modified 14-Jun-94, handle WHEN clause better.
rem Purpose:  This script extracts a trigger definition from the data
rem           dictionary and spools it to an OS file for archiving or
rem           modification.  This version runs against the DBA_x views.

rem Usage: sqlplus -s un/pw _at_$DBA/d_trgddl.sql

rem *******************************************************************

set space 0;
set verify off;
set numwidth 4;
set heading off;
set linesize 80;
set pagesize 0;
set feedback off;
set recsep off;
set long 4000;
set arraysize 1;
ttitle off;
btitle off;

prompt ------------------------------------------------------;

prompt DBA Database Trigger Creation Script Generator;

prompt ------------------------------------------------------;

accept trigowner char prompt 'Trigger Owner: '; accept trigname char prompt 'Trigger Name: '; accept filename char prompt 'Spool to <filename>: ';

prompt ------------------------------------------------------;

prompt Working...;

prompt ------------------------------------------------------;

column remarks format a80;
column col1 noprint;

spool &&filename;

select rpad('rem '||'&&filename',80,' ')||

       rpad('rem '||'Auto-generated on '||sysdate||' by '||user||'.',80,' )||

       rpad('rem '||'Script to create the '||'&&trigowner'||'.'||
         upper('&&trigname')||' trigger.',80,' ')||
       rpad(' ',80,' ') remarks

from dual;

select 0 col1,

         rtrim(decode(rownum, 1,'create or replace trigger ', null)||
           description) description
from     dba_triggers
where    owner = upper('&&trigowner')
and      trigger_name = upper('&&trigname')

union all
select 999 col1,

         rtrim('  when ('||when_clause||')') description
from     dba_triggers
where    owner = upper('&&trigowner')
and      trigger_name = upper('&&trigname')
and      when_clause is not null

order by 1;

select null
from dual;

select trigger_body
from dba_triggers
where owner = upper('&&trigowner')
and trigger_name = upper('&&trigname');

select '/'
from dual;

spool off;

pause Press <Return> to continue;

exit;

---

NOTE NEW E-MAIL ADDRESS: chamilto_at_xis.com

Chris

+---------------------------------------------------+
| Chris Hamilton -- chamilton_at_xis.com               |
| http://www.xis.com/~chamilto/                     |

| Dental Benefit Providers -- City of Washington PB |

+---------------------------------------------------+

Received on Thu Jan 18 1996 - 00:00:00 CET