Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Reverse Engineering Triggers

Re: Reverse Engineering Triggers

From: Robert Pegram <pegramrg_at_yahoo.com>
Date: Tue, 18 Jun 2002 12:08:20 -0800
Message-ID: <F001.00480956.20020618120820@fatcity.com>


Steve,

Here is a korn shell script I use. It also looks at dba_trigger_cols. I was trying to do the same thing a while back, but most of the scripts I found didn't look at dba_trigger_cols. This view becomes important if a trigger does something like 'before update of <column> on <table>'. I will also use export sometimes if the trigger is simple.

HTH
Rob Pegram
Oracle Certified DBA

#!/bin/ksh
#
# Purpose: Create a backup of the existing triggers.
A file will be created
# in ./bu with the source. Use it before
# Create or Replace to save a copy of the
existing.
# If you want all the triggers, change the
trig_cursor to
# where owner not in ('SYS','SYSTEM); and
take out of this
# shell script. Create another one without
owner name do/done, etc
#
# usage dobupkg
#
# prereq's: 1. mkdir bu from the directory you are
# going to run this from
# 2. Create a file allpkg.lst owner name
#

cat alltrg.lst|while read owner name
do
echo $owner $name
sqlplus -s / <<EOF
SET verify off
SET feedback off
SET termout off
SET echo off
SET pagesize 0
SET linesize 132
SET termout off
CREATE TABLE trig_temp (owner varchar2(30),

                        trigger_name varchar2(30),
                        trigger_type varchar2(16),
                        triggering_event varchar2(26),
                        table_owner varchar2(30),
                        table_name varchar2(30),
                        referencing_names
varchar2(87),
                        when_clause varchar2(2000),
                        trigger_body long,
                        trigger_columns varchar2(400))

storage (initial 100k next 100k)
;

DECLARE
   CURSOR trig_cursor
   IS

      SELECT owner,
             trigger_name,
             trigger_type,
             triggering_event,
             'on ' || table_owner,
             table_name,
             referencing_names,
             'when ' || when_clause,
             trigger_body
        FROM dba_triggers
       WHERE owner = upper('$owner')
         AND trigger_name = upper('$name');

   CURSOR trig_col (owner VARCHAR2, name VARCHAR2)    IS

      SELECT trigger_owner, trigger_name, column_name
        FROM dba_trigger_cols
       WHERE trigger_owner = owner
         AND trigger_name = name
         AND column_list = 'YES';

   trig_owner                   
dba_triggers.owner%TYPE;
   trig_name                    
dba_triggers.trigger_name%TYPE;
   trig_type                    
dba_triggers.trigger_type%TYPE;
   trig_event                   
dba_triggers.triggering_event%TYPE;
   trig_towner                  
dba_triggers.table_owner%TYPE;
   trig_tname                   
dba_triggers.table_name%TYPE;
   trig_rnames                  
dba_triggers.referencing_names%TYPE;
   trig_wclause                 
dba_triggers.when_clause%TYPE;
   trig_body                    
dba_triggers.trigger_body%TYPE;
   trig_col_own                 
dba_trigger_cols.trigger_owner%TYPE;
   trig_col_nam                 
dba_trigger_cols.trigger_name%TYPE;
   trig_column                  
dba_trigger_cols.column_name%TYPE;
   all_columns                   VARCHAR2(400);
   counter                       INTEGER := 0;
BEGIN
   OPEN trig_cursor;

   LOOP

      FETCH trig_cursor INTO trig_owner,
                             trig_name,
                             trig_type,
                             trig_event,
                             trig_towner,
                             trig_tname,
                             trig_rnames,
                             trig_wclause,
                             trig_body;
      EXIT WHEN trig_cursor%notfound;
      all_columns := '';
      counter := 0;
      OPEN trig_col (trig_owner, trig_name);

      LOOP
         FETCH trig_col INTO trig_col_own,
trig_col_nam, trig_column;
         EXIT WHEN trig_col%notfound;
         counter := counter + 1;

         IF counter = 1
         THEN
            all_columns := ' of ' || all_columns ||
trig_column;
         ELSE
            all_columns := all_columns || ', ' ||
trig_column;
         END IF;
      END LOOP;

      CLOSE trig_col;

      IF trig_rnames = 'REFERENCING NEW AS NEW OLD AS
OLD'
      THEN
         trig_rnames := '';
      END IF;

      IF trig_wclause = 'when '
      THEN
         trig_wclause := '';
      END IF;

      INSERT INTO trig_temp
           VALUES (
              trig_owner,
              trig_name,
              trig_type,
              trig_event,
              trig_towner,
              trig_tname,
              trig_rnames,
              trig_wclause,
              trig_body,
              all_columns
           );

   END LOOP;    CLOSE trig_cursor;
   COMMIT;
END;
/

DEFINE cr='chr(10)'
SPOOL bu/$owner.$name.trg
SET heading off
SET recsep off pages 0
set long 10000
SELECT

       &&cr ||
       &&cr ||
       'create or replace trigger ' ||
       owner ||
       '.' ||
       trigger_name ||
       &&cr ||
       DECODE (
          trigger_type,
          'BEFORE EACH ROW', 'BEFORE ',
          'AFTER EACH ROW', 'AFTER ',
          trigger_type
       ) ||
       triggering_event ||
       &&cr ||
       trigger_columns ||
       &&cr ||
       table_owner ||
       '.' ||
       table_name ||
       ' ' ||
       referencing_names ||
       &&cr ||
       DECODE (
          trigger_type,
          'BEFORE EACH ROW', 'FOR EACH ROW',
          'AFTER EACH ROW', 'FOR EACH ROW',
          ''
       ) ||
       &&cr ||
       when_clause,
       trigger_body

  FROM trig_temp
 ORDER BY owner;
select '/' from dual;
SPOOL OFF
DROP TABLE trig_temp;
SET verify on
SET feedback on
SET termout on
SET pagesize 22
SET linesize 80
CLEAR columns
exit;
EOF done
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Robert Pegram
  INET: pegramrg_at_yahoo.com

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).
Received on Tue Jun 18 2002 - 15:08:20 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US