Re: Archive Deleted Rows

Adrienne wrote:

> Hello -
> We are looking into a way to create archive tables for records that
> have been deleted off of a table. Currently, when records are deleted
> we set a field called "activation_status" to inactive. The main reason
> why we decided that is to be able to look back as to what old data,
> and not deleting the record. Our thoughts to this so far have been to
> create a trigger on the table to automatically create a record in an
> archive table when a record is deleted. Are there any other
> suggestions or other ways to accomplish an archive of deleted records?
> I'm not an Oracle expert at all, so any help would be much
> appreciated.
>
> Thanks in advance.

Try something like this:

CREATE OR REPLACE TRIGGER aud_employee
AFTER DELETE OR UPDATE
ON employee
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
   rflag VARCHAR2(1);

BEGIN
   IF UPDATING THEN
      rflag := 'U';
   ELSIF DELETING THEN
      rflag := 'D';
   END IF;    INSERT INTO employee_archive
   VALUES

   (:OLD.employee_no, :OLD.last_name, :OLD.first_name,
    :OLD.middle_initial, :OLD.full_name_initials, :OLD.osuser,
    :OLD.emp_address, :OLD.emp_city, :OLD.emp_state,
    :OLD.emp_zip_code, :OLD.contact_phone, :OLD.crew_number,
    :OLD.class_code, :OLD.emp_type, :OLD.emergency_contact,
    :OLD.emp_comments, :OLD.coordinator_flag, :OLD.crew_chief_flag,
    :OLD.engineer_flag, :OLD.default_summit_work_order_no,
    :OLD.default_hrs_per_day, :OLD.default_paycode,
    :OLD.shift_differential, :OLD.active_flag, :OLD.mod_user_id,
    :OLD.mod_user_date, rflag, SYSDATE);

EXCEPTION
   WHEN OTHERS THEN
      NULL;
END aud_employee;
/

Daniel Morgan Received on Thu Aug 01 2002 - 13:05:50 CDT