-- gettrig.sql

SET HEADING OFF
SET NEWPAGE NONE
SET RECSEP OFF
SET VERIFY OFF
SET PAUSE OFF
SET LONG 4000

-- Prompt for the trigger name:
DEFINE trigger_name = &&trigger_name

-- Create an output file for the trigger source code:
SPOOL &trigger_name..sql

-- Trigger source code for &trigger_name
SELECT 'CREATE OR REPLACE TRIGGER ' || description
       || DECODE(when_clause,
                 NULL, '',
                 'WHEN (' || when_clause || ')'
                )
  FROM user_triggers
 WHERE trigger_name = UPPER('&trigger_name');
SELECT trigger_body
  FROM user_triggers
 WHERE trigger_name = UPPER('&trigger_name');
SELECT '/' FROM dual;

SPOOL OFF

PROMPT Trigger source code saved as &trigger_name..sql
UNDEFINE trigger_name


-- For reference...
-- user_triggers
--  Name                            Null?    Type
--  ------------------------------- -------- ----
--  TRIGGER_NAME                    NOT NULL VARCHAR2(30)
--  TRIGGER_TYPE                             VARCHAR2(16)
--  TRIGGERING_EVENT                         VARCHAR2(26)
--  TABLE_OWNER                     NOT NULL VARCHAR2(30)
--  TABLE_NAME                      NOT NULL VARCHAR2(30)
--  REFERENCING_NAMES                        VARCHAR2(87)
--  WHEN_CLAUSE                              VARCHAR2(4000)
--  STATUS                                   VARCHAR2(8)
--  DESCRIPTION                              VARCHAR2(4000)
--  TRIGGER_BODY                             LONG


