Question

Create following table.

CREATE TABLE Registration

(Reg_ID number(5),

Name Varchar2(20),

Address Varchar2(20),

create_date date,

created_by varchar2(10)

);

Create an audit trial report on Employee table for all insert, update and delete operations on given table. You have to create audit table first with Current Date, Operation and User to record the information.

Answer 1

Answer :

To setup the audit table, we need to create a trigger for all of these operations in the main table. This is done by following.

Trigger for Inser operation.

create trigger employeeTriggerAuditRecord on Employee
after insert
as
begin
insert into EmployeeAudit
(CurrentDate, Operation, User) select i.created_date, "INSERTED", i.created_by from Employee e
inner join INSERTED i where e.REG_Id = i.REG_Id
end
go

Similarly for Delete operation.

create trigger employeeTriggerAuditRecord on Employee
after update
as
begin
insert into EmployeeAudit
(CurrentDate, Operation, User) select i.created_date, "UPDATED", i.created_by from Employee e
inner join INSERTED i where e.REG_Id = i.REG_Id
end
go

and Delete

create trigger employeeTriggerAuditRecord on Employee
after DELETE
as
begin
insert into EmployeeAudit
(CurrentDate, Operation, User) select i.created_date, "DELETE", i.created_by from Employee e
inner join DELETED i where e.REG_Id = i.REG_Id
end
go

NOTE : PLEASE GIVE ME UP VOTE .THANK YOU.