AW: Trigger re-engineering

From: Schoen Volker <v.schoen_at_inplan.de>
Date: Fri, 12 May 2000 11:30:40 +0200
Message-Id: <10495.105462@fatcity.com>

Hi Casey,

try this script, this will generate a script of all triggers form a = special
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 =
timing 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 =
timing 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 = would
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 Received on Fri May 12 2000 - 04:30:40 CDT