Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> 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
![]() |
![]() |