rem $DBA/crtrigs.sql
rem
rem This script generates another script that will include all the CREATE
rem TRIGGER statements for those in the database.
rem
rem This script must be run by a user with the DBA role under Oracle7.
rem
rem <<<<<<<<<< Modification History >>>>>>>>>>
rem 01/08/98	Brian Lomasky	Added missing ' OF ' clause before updated
rem				  column name.  Wrap table owner.name to new
rem				  line for readability.
rem 10/21/96	Brian Lomasky	Original
rem
set verify off
set feedback off
set echo off
set pagesize 0
set termout on
select 'Creating trigger build script...' from dual;
create table trig_temp (	lineno NUMBER,
				text VARCHAR2(80));

declare
	cursor trig_cursor is select
		owner,
		trigger_name,
		trigger_type,
		triggering_event,
		table_owner,
		table_name,
		referencing_names,
		when_clause,
		status,
		description,
		trigger_body
		from sys.dba_triggers
		order by 1, 2;

	cursor trig_col_cursor (ownr in varchar2, trigname in varchar2,
		tabown in varchar2, tabnam in varchar2) is select
		column_name
		from	sys.dba_trigger_cols
		where	trigger_owner = ownr and trigger_name = trigname and
			table_owner = tabown and table_name = tabnam;

	lv_owner		sys.dba_triggers.owner%TYPE;
	lv_trigger_name		sys.dba_triggers.trigger_name%TYPE;
	lv_trigger_type		sys.dba_triggers.trigger_type%TYPE;
	lv_triggering_event	sys.dba_triggers.triggering_event%TYPE;
	lv_table_owner		sys.dba_triggers.table_owner%TYPE;
	lv_table_name		sys.dba_triggers.table_name%TYPE;
	lv_referencing_names	sys.dba_triggers.referencing_names%TYPE;
	lv_when_clause		sys.dba_triggers.when_clause%TYPE;
	lv_status		sys.dba_triggers.status%TYPE;
	lv_description		sys.dba_triggers.description%TYPE;
	lv_trigger_body		sys.dba_triggers.trigger_body%TYPE;
	lv_column_name		sys.dba_trigger_cols.column_name%TYPE;
	need_or			boolean;
	comma_needed		boolean;
	break_wanted		boolean;
	lv_lineno		number;
	text_length		number;
	startp			number;
	xchar			number;
	break_pos		number;
	lf_pos			number;
	semi_pos		number;
	lf_break		number;
	backwords		number;
	new_line		number;
	offset			number;
	out_start		number;
	out_len			number;
	l			number;
	out_line		varchar2(2000);
	bef_chars		varchar2(2000);
	a_lin			varchar2(80);
	my_lin			varchar2(2000);
	search_for_break	boolean;
	start_break_search	number;

	function wri(x_lin in varchar2, x_str in varchar2,
		x_force in number) return varchar2 is
	begin
		if length(x_lin) + length(x_str) > 80
		then
			lv_lineno := lv_lineno + 1;
			insert into trig_temp values (lv_lineno, x_lin);
			if x_force = 0
			then
				return x_str;
			else
				lv_lineno := lv_lineno + 1;
				insert into trig_temp values (lv_lineno, x_str);
				return '';
			end if;
		else
			if x_force = 0
			then
				return x_lin||x_str;
			else
				lv_lineno := lv_lineno + 1;
				insert into trig_temp values (
					lv_lineno, x_lin||x_str);
				return '';
			end if;
		end if;
	end wri;

	function brkline(x_lin in varchar2, x_str in varchar2,
		x_force in number) return varchar2 is
	begin
		my_lin := x_lin;
		text_length := nvl(length(x_str), 0);
		-- Break long text field into smaller groups to print
		startp := 1;
		while startp <= text_length
		loop
			-- Assume not backing up to first space
			backwords := 0;
			offset := 0;
			-- Assume forcing a new line
			new_line := 1;
			-- Break line at next space/tab, semi-colon, linefeed,
			-- 80 chars, or end of string
			search_for_break := TRUE;
			start_break_search := startp;
			while search_for_break
			loop
				search_for_break := FALSE;
				break_pos := instr(x_str, ' '||chr(9),
					start_break_search);
				-- Search for next space/tab if no characters
				-- come before it
				if break_pos > 0
				then
					bef_chars := ltrim(substr(x_str,
						start_break_search,
						break_pos - start_break_search +
						1));
					if nvl(bef_chars, '@@xyzzy') = '@@xyzzy'
					then
						break_pos := 0;
						if start_break_search + 2 <
							text_length
						then
							search_for_break :=
							    TRUE;
							start_break_search :=
							    start_break_search
							    + 1;
						end if;
					end if;
				end if;
			end loop;
			lf_pos := instr(x_str, chr(10), startp);
			lf_break := 0;
			-- See if a linefeed break comes before space/tab break
			if (lf_pos < break_pos or break_pos = 0) and lf_pos > 0
			then
				-- Use linefeed as break position search
				break_pos := lf_pos;
				lf_break := 1;
			end if;
			semi_pos := instr(x_str, ';', startp);
			if break_pos + lf_pos = 0 or (break_pos > semi_pos and
				semi_pos > 0)
			then
				if semi_pos = 0
				then
					-- Break line at 80 chars
					break_pos := startp + 80;
					if break_pos > text_length
					then
						break_pos := text_length + 1;
					end if;
					-- Back up to first space
					backwords := 1;
					-- Do not force new line
					new_line := 0;
				else
					-- Assume line breaking at semi-colon
					break_pos := semi_pos + 1;
				end if;
			else
				if lf_break = 0 then
					-- Assume line breaking at space/tab
					break_pos := break_pos + 1;
					-- Skip space part of the space/tab
					offset := 1;
				else
					-- Assume line breaking before linefeed
					-- Skip linefeed
					offset := 1;
				end if;
			end if;
			if break_pos - startp > 80
			then
				break_pos := startp + 79;
				if break_pos > text_length
				then
					break_pos := text_length + 1;
				end if;
				-- Back up to first space
				backwords := 1;
			end if;
			while backwords = 1
			loop
				if break_pos > text_length
				then
					backwords := 0;
					exit;
				end if;
				if break_pos <= startp
				then
					break_pos := startp + 79;
					if break_pos > text_length
					then
						break_pos := text_length + 1;
					end if;
					backwords := 0;
					exit;
				end if;
				if substr(x_str, break_pos, 1) = ' '
				then
					backwords := 0;
					exit;
				end if;
				break_pos := break_pos - 1;
			end loop;
			xchar := break_pos - startp;
			if xchar = 0 then
				if offset = 0 then
					return my_lin;
				end if;
			else
				-- Replace all tabs with 8 spaces in output line
				out_line := replace(substr(x_str, startp,
					xchar), chr(9), '        ');
				-- In case replacing the tabs with spaces caused
				-- the output line to exceed 80 chars, write
				-- 80-col pieces of the translated output line
				out_start := 1;
				l := length(out_line);
				if nvl(l, -1) = -1 then
					return my_lin;
				end if;
				while out_start <= l
				loop
					-- Write output line
					if l >= out_start + 79 then
						out_len := 80;
					else
						out_len := l - out_start + 1;
					end if;
					my_lin := wri(my_lin,
						substr(out_line, out_start,
						out_len), new_line);
					out_start := out_start + out_len;
				end loop;
			end if;
			startp := startp + xchar + offset;
		end loop;
		return my_lin;
	end brkline;

begin
	a_lin := '';
	lv_lineno := 0;
	open trig_cursor;
	loop
		fetch trig_cursor into
			lv_owner,
			lv_trigger_name,
			lv_trigger_type,
			lv_triggering_event,
			lv_table_owner,
			lv_table_name,
			lv_referencing_names,
			lv_when_clause,
			lv_status,
			lv_description,
			lv_trigger_body;
		exit when trig_cursor%NOTFOUND;
		a_lin := wri(a_lin, 'create trigger ', 0);
		a_lin := wri(a_lin, lv_owner || '.' || lv_trigger_name, 1);
		if substr(lv_trigger_type, 1, 6) = 'BEFORE' then
			a_lin := wri(a_lin, ' before', 0);
		else
			a_lin := wri(a_lin, ' after', 0);
		end if;
		need_or := FALSE;
		if instr(lv_triggering_event, 'INSERT') != 0 then
			a_lin := wri(a_lin, ' INSERT', 0);
			need_or := TRUE;
		end if;
		if instr(lv_triggering_event, 'UPDATE') != 0 then
			if need_or then
				a_lin := wri(a_lin, ' OR', 0);
			end if;
			a_lin := wri(a_lin, ' UPDATE OF', 0);
			need_or := TRUE;
			-- See if updating specific column(s)
			comma_needed := FALSE;
			open trig_col_cursor (lv_owner, lv_trigger_name,
				lv_table_owner, lv_table_name);
			loop
				fetch trig_col_cursor into
					lv_column_name;
				exit when trig_col_cursor%NOTFOUND;
				if comma_needed then
					a_lin := wri(a_lin, ',', 0);
				end if;
				a_lin := wri(a_lin, ' ' || lv_column_name, 0);
				comma_needed := TRUE;
			end loop;
			close trig_col_cursor;
		end if;
		if instr(lv_triggering_event, 'DELETE') != 0 then
			if need_or then
				a_lin := wri(a_lin, ' OR', 0);
			end if;
			a_lin := wri(a_lin, ' DELETE', 0);
			need_or := TRUE;
		end if;
		a_lin := wri(a_lin, '', 1);
		a_lin := wri(a_lin, ' on ', 0);
		a_lin := wri(a_lin, lv_table_owner || '.' || lv_table_name, 1);
		break_wanted := FALSE;
		if nvl(lv_referencing_names, ' ') != ' ' then
			if lv_referencing_names !=
				'REFERENCING NEW AS NEW OLD AS OLD'
			then
				a_lin := brkline(a_lin, lv_referencing_names,
					0);
				break_wanted := TRUE;
			end if;
		end if;
		if lv_trigger_type = 'BEFORE EACH ROW' or
			lv_trigger_type = 'AFTER EACH ROW'
		then
			a_lin := wri(a_lin, ' FOR EACH ROW', 0);
			break_wanted := TRUE;
		end if;
		if break_wanted then
			a_lin := wri(a_lin, ' ', 1);
		end if;
		if nvl(lv_when_clause, ' ') != ' ' then
			a_lin := wri(a_lin, ' WHEN (', 0);
			a_lin := brkline(a_lin, lv_when_clause, 0);
			a_lin := wri(a_lin, ')', 1);
		end if;
		a_lin := brkline(a_lin, lv_trigger_body, 0);
		if lv_status = 'DISABLED' then
			a_lin := wri(a_lin, 'alter trigger ', 0);
			a_lin := wri(a_lin, lv_owner || '.' || lv_trigger_name,
				0);
			a_lin := wri(a_lin, ' DISABLE', 0);
			a_lin := wri(a_lin, ';', 0);
		end if;
		-- Skip description (since it's not needed to recreate trigger
		-- a_lin := wri(a_lin, '/* ', 0);
		-- a_lin := brkline(a_lin, lv_description, 0);
		-- a_lin := wri(a_lin, '*/', 1);
	end loop;
	close trig_cursor;
	commit;
exception
	when others then
		rollback;
		raise_application_error(-20000,
			'Unexpected error on ' || lv_owner ||
			'.' || lv_trigger_name || ': ' ||
			to_char(SQLCODE) || ' - Aborting...');
end;
/

set termout off
set heading off
spool cr_trig.sql
select 'rem cr_trig.sql' from dual;
select 'rem' from dual;
select 'rem ***** All database triggers for database ' || name from v$database;
select 'rem' from dual;
select 'set feedback off' from dual;
select text from trig_temp order by lineno;
spool off
drop table trig_temp;
set termout on
select 'Created cr_trig.sql...' from dual;
set termout off
exit