{ Berbagi, Menulis, Dan Mengajar } Ilmu…

SQL>
SQL> create table employee_audit (
2 id VARCHAR2(4 BYTE),
3 salary Number(8,2),
4 delete_date Date,
5 deleted_by VARCHAR2(15 BYTE)
6 )
7 /

Table created.

SQL>
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
2 values ('01','Jason',    'Martin', to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto',  'Programmer')
3 /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
2 values('02','Alison',   'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')
3 /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
2 values('03','James',    'Smith', to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver','Tester')
3 /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
2 values('04','Celia',    'Rice', to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver','Manager')
3 /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
2 values('05','Robert',   'Black', to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver','Tester')
3 /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
2 values('06','Linda',    'Green', to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78,'New York',  'Tester')
3 /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
2 values('07','David',    'Larry', to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78,'New York',  'Manager')
3 /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
2 values('08','James',    'Cat', to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78,'Vancouver', 'Tester')
3 /

1 row created.

SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /

ID   FIRST_NAME LAST_NAME  START_DAT END_DATE      SALARY CITY       DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason      Martin 25-JUL-96 25-JUL-06    1234.56 Toronto    Programmer
02 Alison     Mathews 21-MAR-76 21-FEB-86    6661.78 Vancouver  Tester
03 James      Smith 12-DEC-78 15-MAR-90    6544.78 Vancouver  Tester
04 Celia      Rice 24-OCT-82 21-APR-99    2344.78 Vancouver  Manager
05 Robert     Black 15-JAN-84 08-AUG-98    2334.78 Vancouver  Tester
06 Linda      Green 30-JUL-87 04-JAN-96    4322.78 New York   Tester
07 David      Larry 31-DEC-90 12-FEB-98    7897.78 New York   Manager
08 James      Cat 17-SEP-96 15-APR-02    1232.78 Vancouver  Tester

8 rows selected.

SQL>
SQL>
SQL>
SQL> -- before delete trigger
SQL>
SQL> CREATE OR REPLACE TRIGGER employee_before_delete
2 BEFORE DELETE
3 ON employee
4 FOR EACH ROW
5
6 DECLARE
7 v_username varchar2(10);
8
9 BEGIN
10
11 -- Find username of person performing the DELETE on the table
12 SELECT user INTO v_username
13 FROM dual;
14
15 -- Insert record into audit table
16 INSERT INTO employee_audit (id, salary, delete_date,deleted_by )
17 VALUES (:old.id,:old.salary, sysdate, v_username );
18
19
20
21 END;
22 /

Trigger created.

SQL>
SQL> delete from employee;

8 rows deleted.

SQL>
SQL> select * from employee_audit;

ID       SALARY DELETE_DA DELETED_BY
---- ---------- --------- ---------------
01      1234.56 09-SEP-06 JAVA2S
02      6661.78 09-SEP-06 JAVA2S
03      6544.78 09-SEP-06 JAVA2S
04      2344.78 09-SEP-06 JAVA2S
05      2334.78 09-SEP-06 JAVA2S
06      4322.78 09-SEP-06 JAVA2S
07      7897.78 09-SEP-06 JAVA2S
08      1232.78 09-SEP-06 JAVA2S

8 rows selected.

SQL>
SQL> drop table employee_audit;

Table dropped.

SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /

Table dropped.