| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Reverse Engineering Triggers
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
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
);
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
-- 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
 |
 |