Export Users Trigger Code

核心提示： SET TERMOUT ONPROMPTPROMPT TRIGGERS export utilityPROMPTPROMPT This utilty exports all of the current schema's triggers code intoPROMPT a sub

SET TERMOUT ON

PROMPT

PROMPT TRIGGERS export utility

PROMPT

PROMPT This utilty exports all of the current schema's triggers code into

PROMPT a subdirectory called triggers.

PROMPT

PROMPT Exporting current user's triggers code to folder ./triggers

SET TERMOUT OFF

SPOOL temp_trigger_extract.sql

PROMPT SET ECHO OFF VERIFY OFF FEEDBACK OFF TRIMSPOOL ON TERMOUT OFF PAGES 0

LINES 512

DECLARE

/*
|| This cursor extracts each trigger from user_triggers table
*/
CURSOR cur_trigger_code
IS
SELECT distinct us.trigger_name,
us.trigger_name || '.TRG' spool_file
FROM user_triggers us
ORDER BY us.trigger_name;
BEGIN
FOR cur_trigger_code_row IN cur_trigger_code
LOOP
dbms_output.put_line('spool triggers/' || user || '_' ||
cur_trigger_code_row.spool_file);
dbms_output.put_line('@extract_trigger ' ||
cur_trigger_code_row.trigger_name );
dbms_output.put_line('spool off');
END LOOP;
END;
/
SPOOL OFF
@temp_trigger_extract
SET LONG 80 FEEDBACK ON VERIFY ON TERMOUT ON
PROMPT Export complete!
PROMPT
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
-------------------
extract_trigger.sql
-------------------
SET HEAD OFF VERIFY OFF
SET LONG 1000000
/*
---------------------------------------------------------------------------------------------
--
-- SCRIPT: extract_trigger.sql
--
-- AUTHOR: Alessandro Maestri
-- a.maestri@saritel.it
--
-- PURPOSE: Supports export_triggers.sql, which exports the current
user's triggers
-- code into a subdirectory called .\triggers.
--
-- This script does the actual extraction of the trigger code
and then
-- performs some simple formatting.
--
-- PARAMETERS: Trigger Name
--
-- DEPENDENCIES: none
--
-- REVISIONS:
-- Ver Date Author Description
-- --------- ---------- ------------------
------------------------------------
-- 1.0 03/29/2001 Alessandro Maestri Initial implementation
--
---------------------------------------------------------------------------------------------
*/
SELECT 'CREATE OR REPLACE '|| RTRIM(RTRIM(SUBSTR(us.description,13),
CHR(10) )),
us.trigger_body
FROM user_triggers us
WHERE us.trigger_name = '&1';
PROMPT /
PROMPT

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%