Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Archive Deleted Rows

Re: Archive Deleted Rows

From: Daniel Morgan <dmorgan_at_exesolutions.com>
Date: Thu, 01 Aug 2002 18:05:50 GMT
Message-ID: <3D49787C.EE12268E@exesolutions.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US