#
# Name          : dbc_gen_chg_trigger.shl
# Purpose       : Create the change triggers for table {desct} with owner
#                 {desco} and fields listed in dbc_gen_triggers.lst.
# Revisions
# Date          Modified by     Reason
# 25-Jan-00     Stephen Rea     Y2K Fixes
#
cat dbc_gen_triggers.lst | read -r desco desct
echo "drop trigger ${desco}.dbc_${desct}_chg_trigger;"
echo ""
echo "create trigger ${desco}.dbc_${desct}_chg_trigger"
echo "after update on ${desco}.${desct} for each row"
echo "when (substr(user,1,6) <> 'PDPUSR')"
echo "declare"
echo "  user_name  varchar2(20);"
echo "  chg_date   date;"
echo "  key        number(8);"
echo "  field_seq  number(3);"
echo "begin"
echo "  select substr(user,1,10) into user_name from dual;"
echo "  select sysdate into chg_date from dual;"
echo "  "
echo "  key := :old.${desct}_pidm;"
echo "  field_seq := 0;"
echo "  "
cat dbc_gen_triggers.lst | while read -r desc1 desc2 desc3 desc4 desc5 desc6
do
  if [ "$desc1" != "$desco" -a "$desc2" != "$desct" ]
  then
    for descx in $desc2 $desc3 $desc4 $desc5 $desc6
    do
      if [ -n "$descx" ]
      then
        if [ `echo "  $descx" | grep -c CHAR` -eq 1 ]
        then
          echo "  if :old.${desc1} <> :new.${desc1} then"
          echo "    field_seq := field_seq + 1;"
          echo "    insert into system.dbc_changes values"
          echo "      (user_name,chg_date,'${desc1}',field_seq,'C',"
          echo "      substr(:old.rowid,1,8) || substr(:old.rowid,10,4),"
          echo "      substr(:old.${desc1},1,50),substr(:new.${desc1},1,50),"
          echo "      key);"
          echo "  end if;"
        elif [ `echo "  $descx" | grep -c NUMBER` -eq 1 ]
        then
          echo "  if :old.${desc1} <> :new.${desc1} then"
          echo "    field_seq := field_seq + 1;"
          echo "    insert into system.dbc_changes values"
          echo "      (user_name,chg_date,'${desc1}',field_seq,'C',"
          echo "      substr(:old.rowid,1,8) || substr(:old.rowid,10,4),"
          echo "      substr(:old.${desc1},1),substr(:new.${desc1},1),"
          echo "      key);"
          echo "  end if;"
        elif [ `echo "  $descx" | grep -c DATE` -eq 1 ]
        then
          echo "  if :old.${desc1} <> :new.${desc1} then"
          echo "    field_seq := field_seq + 1;"
          echo "    insert into system.dbc_changes values"
          echo "      (user_name,chg_date,'${desc1}',field_seq,'C',"
          echo "      substr(:old.rowid,1,8) || substr(:old.rowid,10,4),"
          echo "      to_char(:old.${desc1},'mm/dd/yyyy'),to_char(:new.${desc1},'mm/dd/yyyy'),"
          echo "      key);"
          echo "  end if;"
        fi
      fi
    done
  fi
done
echo "end;"
echo "/"