Lets create a table :
CREATE TABLE "ABC"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10 BYTE),
"JOB" VARCHAR2(9 BYTE),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
) TABLESPACE "USERS" ;
Now we will insert some data into it :
Insert into ABC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_date('17-DEC-80','DD-MON-RR'),800,null,20);
Insert into ABC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,to_date('20-FEB-81','DD-MON-RR'),1600,300,30);
Insert into ABC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,to_date('22-FEB-81','DD-MON-RR'),1250,500,30);
Insert into ABC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_date('02-APR-81','DD-MON-RR'),2975,null,20);
Insert into ABC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,to_date('28-SEP-81','DD-MON-RR'),1250,1400,30);
select count(*) from abc;
-- 5 rows
Lets create a backup table to store data. we are only storing part of the original table data, we can edit as per requirement :
CREATE TABLE "SCOTT"."ABC_BAK"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10 BYTE),
"JOB" VARCHAR2(9 BYTE),
"MGR" VARCHAR2(20 BYTE),
"T_STAMP" TIMESTAMP (6) DEFAULT SYSTIMESTAMP
) TABLESPACE "USERS" ;
select count(*) from abc_bak;
0 rows
Now lets create a trigger, that will triger all the data from the table and store it in backup table before delete or update:
CREATE OR REPLACE TRIGGER ABC_BAK1
BEFORE UPDATE OR DELETE
ON ABC FOR EACH ROW
BEGIN
INSERT INTO ABC_BAK
( EMPNO,
ENAME,
JOB,MGR
)
VALUES
( :old.EMPNO,
:old.ENAME,
:old.JOB,
:old.MGR);
END;
===========================================================
NOTE : You also add username and Host machine by adding below to trigger
You need to declare the variable first and then assgn values to it
DECLARE
v_username varchar2(10);
BEGIN
-- Find username of person performing the DELETE on the table
SELECT user INTO v_username FROM dual;
Add these to values ----
V_USERNAME,
sys_context('userenv','host') --- You can also add host machine here
===========================================================
Lets test our trigger is working fine.
delete from abc where ename=SCOTT;
delete from abc where ename=ALENN;
update abc set ename=ADAM where ename=ADAMS;
.
.
.
.
do some activity and test then validate the bak table.
Select * from abc_bak;
EMPNO ENAME JOB MGR T_STAMP
---------- ---------- --------- -------------------- -------------------------------
7788 SCOTT 09-FEB-15 06.35.28.862219000 PM
7499 ALLEN 09-FEB-15 06.35.28.862219000 PM
7521 WARD 09-FEB-15 06.35.28.862219000 PM
7876 ADAMS CLERK 09-FEB-15 06.35.53.457126000 PM
7900 JAMES CLERK 09-FEB-15 06.35.53.462433000 PM
7902 FORD ANALYST 09-FEB-15 06.35.53.466738000 PM
7934 MILLER CLERK 09-FEB-15 06.35.53.472108000 PM
7788 SCOTT ANALYST 7566 09-FEB-15 06.37.30.307425000 PM
8 rows selected
