Delete Trigger on Oracle Database Tables - Record deleted rows from your database

I have been very busy with work lately and I had put my blogging career on hold, I am back now..

For this article I will try to be very precise two simple steps.

STEP ONE

Have a similar table as the table you are listening in for delete actions. On your new backup table you might want to have more fields like the delete_user, the computer_name where this was done and the time_stamp.
You can easily create the backup table by copying the existing table then adding the extra columns.
There are many ways to do this so i will let you decide on which one you prefer but I would normally quickly do it as follows;

Run the following sql
Create table mytablename_after_delete as select * from mytable;
--then  empty the table
TRUNCATE TABLE mytablename_after_delete;
--the add your extra columns using alter command
alter table mytablename_after_delete add sysdate timestamp; --you can make it varchar if need be
alter table mytablename_after_delete add deleted_by varchar(30);
--add as many columns as you need.

STEP TWO

Once your table is done proceed with setting up the trigger.

CREATE OR REPLACE TRIGGER mytablename_after_delete
AFTER DELETE
   ON mytablename
   FOR EACH ROW
--declare your variables like machine names and user names
DECLARE
   v_username varchar2(10);
--declared username of delete user
BEGIN

   -- Find username of person performing the DELETE on the table
   SELECT user INTO v_username
   FROM dual;

   -- Insert record into backup mytablename_after_delete table
   INSERT INTO mytablename_after_delete
   ( mytablename_fieldnames,
--enter all fields in your original table here the new backup table should have similar fields or more since you now have delete user and time
delete_date,
deleted_by)
   VALUES
   (
--to fetch your deleted fields you must start with semicolon and the keyword old
:old.mytablename_fieldname,
--add all your fields as above
sysdate,
--remember we did not initialize sysdate as this does not require declaration
v_username );

END;


As you can see i made it very brief, in case you have any questions please feel free to ask.
I will be more than delighted to share the little knowledge i have.