Hi Ankit,
Solution given by Connor is no code solution. Any thing that can be done without code is better (less code = less bugs). Any way this might help you.
create Table A
(emp_id number(5) PRIMARY KEY,
emp_name varchar2(30),
dept_id number(2),
salary number(5) )
/
CREATE Table B
(emp_id number(5),
emp_name varchar2(30),
dept_id number(2),
salary number(5),
Old_value number(5),
New_value number(5),
action VARCHAR2(10) )
/
insert into A
values (1 , 'A' , 10 , 1000);
insert into A
values (2 , 'B' , 20 , 2000);
insert into A
values (3 , 'C' , 30 , 3000);
insert into A
values (4 , 'D' , 40 , 4000);
COMMIT
/
CREATE OR REPLACE PROCEDURE INSERT_A(
emp_id_in A.emp_id%type,
emp_name_in A.emp_name%type,
dept_id_in A.dept_id%type,
salary_in A.salary%type)
IS
BEGIN
insert into a(emp_id, emp_name, dept_id, salary)
values(emp_id_in, emp_name_in, dept_id_in, salary_in) ;
insert into b(emp_id, emp_name, dept_id, salary,Old_value,new_value, action)
values(emp_id_in, emp_name_in, dept_id_in, salary_in,null, salary_in, 'INSERT') ;
END;
/
begin
insert_a(5,'E',50,'5000');
end;
/
select * from a ;
select * from b ;
/
CREATE OR REPLACE PROCEDURE update_A(
emp_id_in A.emp_id%type,
salary_in A.salary%type)
IS
l_emp_name A.emp_name%type ;
l_dept_id A.dept_id%type ;
l_salary A.salary%type ;
BEGIN
SELECT emp_name, dept_id, salary
into l_emp_name, l_dept_id, l_salary
from A
where emp_id = emp_id_in
for update;
update a
set salary = salary_in
where emp_id = emp_id_in ;
insert into b(emp_id, emp_name, dept_id, salary,Old_value,new_value, action)
values(emp_id_in, l_emp_name, l_dept_id, l_salary,l_salary, salary_in, 'UPDATE') ;
END;
/
begin
update_a(5,'6000');
end;
/
select * from a ;
select * from b ;
CREATE OR REPLACE PROCEDURE delete_A(
emp_id_in A.emp_id%type )
IS
l_emp_name A.emp_name%type ;
l_dept_id A.dept_id%type ;
l_salary A.salary%type ;
BEGIN
SELECT emp_name, dept_id, salary
into l_emp_name, l_dept_id, l_salary
from A
where emp_id = emp_id_in
for update;
delete from a
where emp_id = emp_id_in ;
insert into b(emp_id, emp_name, dept_id, salary,Old_value,new_value, action)
values(emp_id_in, l_emp_name, l_dept_id, l_salary,l_salary, NULL, 'DELETE') ;
END;
/
begin
DELETE_a(5);
end;
/
select * from a ;
select * from b ;