#!/usr/bin/ksh
#
#@(#) File: build_triggers_v2.ksh 
#
#@(#) %W% Copyright (c) 2001 %E% Jeffery J. Jimes
#
# Author: Jeffery J. Jimes
# Date: 08/27/2001
# Usage: $ build_triggers_v2.ksh > create_analyze_triggers.sql
#
# Operating System: Solaris
#
# Description: 
# This script is used to generate a script that will create insert and update triggers
# for all tables in a oracle database.
#
# History
# Date Who Description
# 08/27/2001 Jimes Creation
# 



ORACLE_LOGIN=user_id/password

#
# Get a list of all oracle tables
#
LIST=$( sqlplus -s << EOFSQL
ORACLE_LOGIN
SET HEADING OFF
SET FEEDBACK OFF
SET PAUSE OFF
WHENEVER SQLERROR EXIT FAILURE;
select table_name from user_tables
/
EOFSQL
)

#
# For each table in a list generate a trigger (Q will remove duplicate names )
#

Q=0
for y in $LIST
do
Q=`expr $Q + 1` 
x=$(echo $y | awk '{print substr($NF,1,19)}')
print " "
print "CREATE TRIGGER trg_ins_"$x$Q
print "AFTER INSERT"
print "ON "$y
print "REFERENCING NEW AS NEW OLD AS OLD"
print "FOR EACH ROW"
print "BEGIN"
print " insert into trigger_history values ('"$y"',sysdate,to_char(sysdate,'HH:MI:SS'), 'INSERT');"
print "END;"
print "/"
print " "
print " "
print "CREATE TRIGGER trg_upd_"$x$Q
print "AFTER UPDATE"
print "ON "$y
print "REFERENCING NEW AS NEW OLD AS OLD"
print "FOR EACH ROW"
print "BEGIN"
print " insert into trigger_history values ('"$y"',sysdate,to_char(sysdate,'HH:MI:SS'), 'UPDATE');"
print "END;"
print "/"
print " "
done