KD09714
Profile Answers by KD09714 Questions by KD09714

Jun 8th, 2008

Yes using pragma autonomous_transaction as per example below for commit.

we have two table employee and employee_bak of same structure
(empid, emp_name, location, manager_id)

--autonomous procedure will insert value in employee_bak, procedure is getting --called from trigger on insert in employee.

create or replace procedure replicate_employee
(p_emp_id employee.emp_id%type,
p_emp_name employee.emp_name%type,
p_location employee.location%type,
p_manager_id employee.manager_id%type)
as
pragma autonomous_transaction;
begin
insert into employee_bak values(p_emp_id, p_emp_name, p_location, p_manager_id);
commit;
end replicate_employee;

--trigger after insert on employee

create or replace trigger trg_backup_employee
after insert on employee
for each row
declare
begin

replicate_employee(:new.emp_id, :new.emp_name, :new.location, :new.manager_id);

end;

--Note : if pragma autonomous_transaction is not used, trigger will give error at run --time