Oracle: How to execute an insert trigger without delaying the insert response?

There is a perfect solution for this exact situation called Database Change Notification You can think of it almost exactly like an async trigger You use the DBMS_Change_Notification package to tell oracle which tables to watch and what to do when a change occurs. You can monitor for DML and DDL, you can have Oracle batch the changes (i.e. Wait for 10 changes to occur before firing).

It will call a sproc with an object containing all the rowids of the changed rows... you can decide how to handle, including calling HTTP. It will not have to finish for the insert to commit Documentation for 10gR2.

There is a perfect solution for this exact situation called Database Change Notification. You can think of it almost exactly like an async trigger. You use the DBMS_Change_Notification package to tell oracle which tables to watch and what to do when a change occurs.

You can monitor for DML and DDL, you can have Oracle batch the changes (i.e. Wait for 10 changes to occur before firing). It will call a sproc with an object containing all the rowids of the changed rows... you can decide how to handle, including calling HTTP.It will not have to finish for the insert to commit.

Documentation for 10gR2.

Thanks! I saw it after your comment on my blog – Jader Dias Nov 2 '09 at 23:36.

One approach is to have the trigger create a dbms_job that runs once (each) time to perform the http transfer. The dbms_job creation is relatively quick and you can think of this as effectively spawning a new thread in parallel. See asktom.oracle.com/pls/asktom/f?p=100:11:... for further info - his example deals with sending email, but the idea is the same.

The Trigger has an advantage of executing earlier, which is important to me. – Jader Dias Aug 7 '09 at 13:41 if you have sufficient job_queue_processes available the job will be picked up with no significant delay - creating the job from the trigger does participate in the overall transaction however, so the creation of the job will be delayed until the commit (but this is mostly a good thing, since a rollback will not create the job/initiate the transfer) – dpbradley Aug 7 '09 at 13:53 it will do, I will try, thanks – Jader Dias Aug 7 '09 at 13:55 1 If you are on 10G or above you might have a look at the job scheduler before you use dbms_job, I haven't played with it too much yet but there may be more options and it is the replacement for dbms_job. You could also look at using queuing or dbms_pipe to set up a process to handle the request almost instantly.

– Ethan Post Aug 7 '09 at 18:00 1 Yes, dbms_scheduler does a commit when you SUBMIT the job, dbms_job doesn't. I really think you should not use dbms_scheduler in this case. See also asktom.oracle.Com/pls/asktom/… – tuinstoel Aug 7 '097 at 11:30.

Maybe you could create a local table that store the info do you have to transfer, and create a job that executes every X minutes. The job read from the table, transfer all the data and delete the transfered data from the table.

You send your inserted data via http to a remote location in an after or before statement trigger. What will happen when there is a rollback? Your hhtp send message will not be rollbacked so you have inconsistent data.

– Jader Dias Aug 7 '09 at 13:34 You can use Oracle Streams to sync two or more databases that are geoghraphically separated, the internet will be used. – tuinstoel Aug 7 '09 at 14:08.

Well obviously, you could prevent the delay by removing the Trigger.... Else, the trigger will ALWAYS be executed before your insert, thats what the TRIGGER BEFORE INSERT is made for. Or maybe you could give us more details on what you need exactly?

I need to transfer all data that is inserted in my table to a remote location which has an HTTP interface – Jader Dias Aug 7 '09 at 13:21 Well, since the HTTP thing would be triggered AFTER the insert, the insert won't be delayed anymore by the HTTP request.So it could be worth a try. – guigui42 Aug 7 '09 at 13:28 strangely it didn't work – Jader Dias Aug 7 '09 at 13:31 2 The AFTER trigger would not be any better. So the row is inserted, but not commited, what's the advantage of that?

– ammoQ Aug 7 '09 at 13:32 @ammoQ Thanks for explaining why it does not work – Jader Dias Aug 7 '09 at 13:36.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.