-- THIS IS NOT OFFICIAL ORACLE SCRIPT
-- This document was written by Steven Karam, and is not endorsed by Oracle
-- Oracle Corporation makes no guarantees of any sort on this, and can not be held responsible
-- for its contents or any consequences of using the script contained herein.
-- This is an official note that this script is not official or unofficial Oracle material
-- in any way, shape, or form.

-- FUNCTIONALITY:  This script will add three columns to each table in your schema; CREATE_DATE, LAST_UPDATE, and LAST_IP.
-- It will also create a trigger on each table that will auto-populate these new columns with SYSDATE on insert (except for LAST_IP)
-- On update, LAST_UPDATE will update to the current SYSDATE.  CREATE_DATE will be uneditable, and will always revert
-- to the original.  This is good for row level auditing of records.  LAST_IP will provide the IP address of the last person to
-- insert or update to this table, if available.

declare
  cursor c is select table_name from user_tables;
begin
  for i in c loop
    begin
      execute immediate 'alter table '||i.table_name||' add last_update date';
      execute immediate 'alter table '||i.table_name||' add create_date date';
      execute immediate 'alter table '||i.table_name||' add last_ip varchar2(25);
      execute immediate 'create or replace trigger predates_'||i.table_name||' before insert or update on '||i.table_name||' for each row 
                         begin
                           if inserting then
                             :new.create_date := sysdate;
                           elsif updating then
                             :new.create_date := :old.create_date;
                           end if;
                           :new.last_update := sysdate;
                           begin
                             select sys_context(''userenv'', ''ip_address'') into :new.last_ip from dual;
                           exception when no_data_found then null;
                           end;
                         end;';
    exception when others then
      dbms_output.put_line('Table '||i.table_name||' failed.');
    end;
  end loop;
end;
/