Re: AW: Trigger re-engineering

From: Casey Dyke <cdyke_at_homenetwork.com.au>
Date: Fri, 19 May 2000 20:15:56 +1000
Message-Id: <10502.106065@fatcity.com>

This is a multi-part message in MIME format.

--------------0BCFAB446D3E9425736D6EDC
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

X-MIME-Autoconverted: from 8bit to quoted-printable by unix7.corp.au.home.com id UAA19840

Schoen,

Thanks for the response, been on the 8i new features course all week - he= nce
the late reply. Cut my own code in the end, it's attached fyi ... did th= e job
nicely!

Cheers,

Casey ...

Schoen Volker wrote:

> Hi Casey,
>
> try this script, this will generate a script of all triggers form a spe=
cial
> schema.
>
> spool c:\temp\Trigger.sql
> col c1 format a79 word_wrap
> set long 32000
> set arraysize 1 verify off feedback off echo off heading off timin=
g off
> prompt REM Generating Triggers
>
> select 'create or replace trigger ' c1,
> description c1,
> 'WHEN ('||when_clause||')' c1,
> trigger_body ,
> '/' c1,
> 'show errors;' c1,
> 'commit;' c1
> from user_triggers
> where when_clause is not null
> /
>
> col c1 format a79 word_wrap
> set long 32000
> set arraysize 1 verify off feedback off echo off heading off timin=
g off
> prompt REM Generating Triggers
> select 'create or replace trigger ' c1,
> description c1,
> trigger_body ,
> '/' c1,
> 'show errors;' c1,
> 'commit;' c1
> from user_triggers
> where when_clause is null
> /
>
> Mit freundlichen Gr=FC=DFen
>
> i. V. Volker Sch=F6n
> INPLAN RUHR
> Informationstechnik GmbH
> Tel.: +49 208 / 65 91 - 950
> Fax: +49 208 / 65 91 - 980
> E-Mail: mailto:v.schoen_at_inplan.de
> http://www.inplan.de
>
> -----Urspr=FCngliche Nachricht-----
> Von: Casey Dyke [mailto:cdyke_at_homenetwork.com.au]
> Gesendet: Donnerstag, 11. Mai 2000 12:25
> An: Multiple recipients of list ORACLE-L
> Betreff: Trigger re-engineering
>
> Folks,
>
> Being lazy here as I could do it myself, but I just realised I need to
> do this and as I don't have a script handy, not farting around w/it in
> the morning would be handy. Just need to rev-eng some triggers out of
> the dd. Had a look (8.0.5) and the trigger_type and triggering_event
> don't seem to give me what I want syntactically. Looks like I get
> 'BEFORE EACH ROW' from type and 'INSERT' from triggering_event.
>
> Guess I need syntax like 'create or replace ... before insert on
> <table_name> for each row'. In my naiveity, I thought the columns woul=
d
> front the info up and I could whip through it easy. Looks to be a
> little more tricky, unless I'm not looking at the whole picture. Seems
> odd to me, but I've had quite a long day ...
>
> TIA,
>
> Casey ...
> --
> Author: Schoen Volker
> INET: v.schoen_at_inplan.de
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

--------------0BCFAB446D3E9425736D6EDC
Content-Type: text/plain; charset=us-ascii;  name="ora_rev_eng_trig.sql"
Content-Disposition: inline;
 filename="ora_rev_eng_trig.sql"
Content-Transfer-Encoding: 7bit

rem Reverse engineer triggers

set serveroutput on size 1000000

DECLARE   CURSOR trig_cur IS
    SELECT *
    FROM user_triggers;

  trig_rec   trig_cur%ROWTYPE;
  trig_body1 VARCHAR2(32767);
  trig_body2 VARCHAR2(32767);

  FILEhandle    UTL_FILE.file_type;                                             
  StrBuffer     varchar2(50);
  UTLdest       varchar2(40) := '/tmp/';

BEGIN   FILEhandle := utl_file.fopen(UTLdest, 'rev_eng_trig.sql', 'W');

  OPEN trig_cur;
  LOOP     FETCH trig_cur INTO trig_rec;
    EXIT WHEN trig_cur%NOTFOUND;

    trig_body1 := 'prompt ** Trigger creation for '||trig_rec.trigger_name;
    trig_body1 := trig_body1||chr(10); 
    trig_body1 := trig_body1||chr(10); 
    trig_body1 := trig_body1||'CREATE OR REPLACE TRIGGER '||trig_rec.trigger_name;
    trig_body1 := trig_body1||chr(10); 
    trig_body1 := trig_body1||substr(trig_rec.trigger_type,1, (ltrim(rtrim(instr(trig_rec.trigger_type,' ')))-1)); 
    trig_body1 := trig_body1||' '||trig_rec.triggering_event;
    trig_body1 := trig_body1||chr(10); 
    trig_body1 := trig_body1||'ON '||trig_rec.table_owner||'.'||trig_rec.table_name;
    trig_body1 := trig_body1||chr(10); 
    trig_body1 := trig_body1||'FOR ';
    trig_body1 := trig_body1||substr(trig_rec.trigger_type,(ltrim(rtrim(instr(trig_rec.trigger_type,' ')))+1),length(trig_rec.trigger_type));
   

    trig_body2 := trig_rec.trigger_body;

    utl_file.put_line(FILEhandle, trig_body1);                               
    utl_file.put_line(FILEhandle, trig_body2);                               
    utl_file.put_line(FILEhandle, '/');                               

  END LOOP;
  CLOSE trig_cur;
  utl_file.fclose(FILEhandle);

EXCEPTION   WHEN OTHERS THEN

    IF utl_file.is_open(FILEhandle) THEN                                        
      utl_file.fclose(FILEhandle);                                              
    END IF;                          

    dbms_output.put_line('Error: '||SQLERRM);

END;
/

--------------0BCFAB446D3E9425736D6EDC
Content-Type: text/x-vcard; charset=us-ascii;  name="cdyke.vcf"
Content-Description: Card for Casey Dyke Content-Disposition: attachment;
 filename="cdyke.vcf"
Content-Transfer-Encoding: 7bit

begin:vcard
n:Dyke;Casey

tel;pager:(022) 9436 9290
tel;home:61 2 9948 1487
tel;work:61 2 9005 1021

x-mozilla-html:FALSE

url:www.realsurf.com
org:At Home Network Australia;IT-Ops
adr:;;100 Harris Street;Pyrmont;NSW;2042;Australia

Received on Fri May 19 2000 - 05:15:56 CDT