PL/SQL Audit Trigger

In previous topic we discuss the audit of table when inserting, updatind, or deleting any row. If we want to save the audition in another table then
First of all we have to create a table.

Create Table:
create table audit_heros
(
new_name varchar2(30),
old_name varchar2(30),
user_name varchar2(30),
operation_date varchar2(30),
operation varchar2(30)
);

/
Result:
Table audit_heros created.

Now we create an audit trigger on audit heros, that keeps the record of changing the heros table.

Audit Trigger:
create or replace trigger au_heros
before insert or delete or update on heros
for each row
enable
declare
v_user varchar2(30);
v_date varchar2(30);
begin
select user, to_char(sysdate,'DD/MM/YYYY HH24:MI:SS') into v_user,v_date from dual;
if inserting then
  insert into audit_heros values(:NEW.hero_name,'NULL',v_user,v_date,'INSERT');
elsif deleting then
  insert into audit_heros values('NULL',:OLD.hero_name,v_user,v_date,'DELETE');
elsif updating then
  insert into audit_heros values(:NEW.hero_name,:OLD.hero_name,v_user,v_date,'update');
end if;
end;

/
Result:
Trigger au_heros  compiled.

Now by inserting or deleting or updating the row in heros table. we can check the working of au_heros trigger.
When Deleting:
delete from heros where hero_name='Salman';

Result:
3 rows deleted.
Now chek audit_heros table

select * from audit_heros;
Result:

NULL Salman HR 13/05/2017 16:34:45 DELETE
NULL Salman HR 13/05/2017 16:34:45 DELETE
NULL Salman HR 13/05/2017 16:34:45 DELETE

When updating:
update heros set hero_name='Aybue khan' where hero_name='Ayube';

And again check the audit_heros table
select * from audit_heros;
Result:

NULL          Salman HR 13/05/2017 16:34:45 DELETE
NULL          Salman HR 13/05/2017 16:34:45 DELETE
NULL          Salman HR 13/05/2017 16:34:45 DELETE

Aybue khan Ayube HR 13/05/2017 16:40:56 update

Note:
NEW keyword allows you access a row that is being proceed . It means that it allow acess to a row that is being inserted or updated in the table

OLD keyword allow access to a table that is already inserted or updated in the table.