Friday 12 July 2013

How do you call procedure have a DDL or commit/rollback statement from a trigger?

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

No comments:

Post a Comment