--DESCRIPTION
--This script lists all the triggers defined on the specified table.
--
--USAGE
--	@list_triggers [owner.]table_name
--

SET ECHO OFF
SET HEADING OFF
SET RECSEP OFF
SET NEWPAGE 1
SET VERIFY OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET LONG 10000
SET LINESIZE 80

--Dissect the input argument, and get the owner name and
--table name into two separate substitution variables.
--The owner name defaults to the current user.
DEFINE s_owner_name = ' '
DEFINE s_table_name = ' '
COLUMN owner_name NOPRINT NEW_VALUE s_owner_name
COLUMN table_name NOPRINT NEW_VALUE s_table_name
SELECT
  DECODE(INSTR('&&1','.'),
         0,USER,  /*Default to current user.*/
	 UPPER(SUBSTR('&&1',1,INSTR('&&1','.')-1))) owner_name,
  DECODE(INSTR('&&1','.'),
         0,UPPER('&&1'),  /*Only the table name was passed in.*/
	 UPPER(SUBSTR('&&1',INSTR('&&1','.')+1))) table_name
  FROM DUAL;

--Set up the formats
COLUMN description FORMAT A60 WORD_WRAPPED
COLUMN referencing_names FORMAT A60 WORD_WRAPPED FOLD_BEFORE
COLUMN when_clause FORMAT A60 WORD_WRAPPED FOLD_BEFORE
COLUMN trigger_body FORMAT A80 WRAPPED FOLD_BEFORE

SELECT 'TRIGGER ' || '&&s_owner_name..' ||
       RTRIM(description) description,
       RTRIM(referencing_names) referencing_names,
       'WHEN ' || when_clause when_clause,
       trigger_body
  FROM all_triggers
 WHERE owner = '&&s_owner_name'
   AND table_name = '&&s_table_name';

--Reset everything back to its default setting
TTITLE OFF
SET HEADING ON
SET RECSEP WRAPPED
SET VERIFY ON
SET FEEDBACK ON
SET PAGESIZE 24
SET LONG 80
CLEAR BREAKS
CLEAR COLUMNS
