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
--- "Orr, Steve" <[EMAIL PROTECTED]> wrote:
> Anyone have a handy-dandy script to create a script
> which recreates
> triggers? (By querying user/dba_triggers.)
>
> Member of the Wheel Reinvention Prevention Society,
> Steve Orr
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Orr, Steve
> INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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).
__________________________________________________
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
