I am trying to setup a trigger such that whenever a record is inserted or updated on a table, the trigger will call a stored procedure (uses UTL.HTTP) which sends the inserted/updated ID (PK) to a web service Then the web service will execute a process to grab the inserted or updated data (other columns in addition to the ID) from the database. In order to achieve this, the trigger needs to be executed after the commit of the base table otherwise the web service will get the “before image" data.
I’ve read that this can be done by using a trigger on a materialized view with ON COMMIT refresh. I tried that but it looks like the base table only commits AFTER the materialized view refresh/commit is done. So the web service is still getting the “before image" of the data. Below is how I create the materialized view. Is there something wrong with it or this is just the way materialized work (base table commit after mview refresh completes)? And is there any alternative to implement this? Thanks.
create materialized view log on employee;
create materialized view mv_employee
refresh fast on commit
as select * from employee;
CREATE OR REPLACE TRIGGER trigger_mv_employee
after insert or update
ON mv_employee
for each row
declare
begin
push_employeeid_to_web_service(:NEW.employeeid);
end;
/