set showmode off echo off
set heading off pagesize 0 timing off feedback off linesize 80
Rem
Rem Script Name   : dbc_gen_triggers.sql
Rem Authors Name  : Stephen Rea
Rem Purpose       : Generate the change triggers (dbc_gen_chg_trigger.sql),
Rem                 insert/delete triggers (dbc_gen_id_trigger.sql), and
Rem                 default field report sets (dbc_gen_report_set.sql) for
Rem                 a given table name and table owner.
Rem
Rem Modification History:
Rem Date        Modified by     Reason
Rem 1/25/00     Stephen Rea     Added table owner prompt.
Rem
accept tablename char prompt 'Enter table name to generate (only one): '
set termout off verify off
column tablenameset new_value tablename
select upper('&tablename') tablenameset from dual;
define tableowner = 'DUMMY'
column tableownerset new_value tableowner
select owner tableownerset from dba_tables
  where table_name = upper('&tablename') and rownum = 1;
select username tableownerset from user_users
  where '&tableowner' = 'DUMMY' and rownum = 1;
set termout on
accept tableowner2 char prompt 'Enter table owner, if not &tableowner: '
set termout off
select upper(decode('&tableowner2','','&tableowner','&tableowner2'))
  tableownerset from dual;
spool dbc_gen_triggers.lst
select '&tableowner &tablename' from dual;
desc &tableowner..&tablename
spool off
!echo "Generating change triggers..."
!sh dbc_gen_chg_trigger.shl >dbc_gen_chg_trigger.sql
!echo "Generating insert/delete triggers..."
!sh dbc_gen_id_trigger.shl >dbc_gen_id_trigger.sql
!echo "Generating default field report sets..."
!sh dbc_gen_report_set.shl >dbc_gen_report_set.sql
!echo ""
!echo "To create the change and insert/delete triggers:"
!echo "  @dbc_gen_chg_trigger.sql"
!echo "  @dbc_gen_id_trigger.sql"
!echo "  @dbc_gen_report_set.sql"
!echo ""
set termout on
accept ans char prompt 'Run generate trigger sql above (y or n)? '
set termout off
spool dbc_gen_triggers_do.sql
select '@dbc_gen_chg_trigger.sql' from dual where lower('&ans') = 'y';
select '@dbc_gen_id_trigger.sql' from dual where lower('&ans') = 'y';
select '@dbc_gen_report_set.sql' from dual where lower('&ans') = 'y';
select 'commit;' from dual where lower('&ans') = 'y';
select '!echo "Triggers generated"' from dual where lower('&ans') = 'y';
select '!echo "Triggers not yet generated"' from dual where lower('&ans') != 'y';
spool off
@dbc_gen_triggers_do.sql
set linesize 80
set termout on
set heading on
set pagesize 24
set timing on
set feedback 6
set verify on
set echo on
set showmode both