I’m trying to develop a flow that is kicked of by a trigger on a table. The trigger kicks off the flow by passing three variables through a http invoke. (e.g. SELECT '[url=“http://170.121.201.18:5555/invoke/TrapezeToMMMS.Trans:Main?vehicleID=”]http://170.121.201.18:5555/invoke/TrapezeToMMMS.Trans:Main?vehicleID=[/url]‘||:new.vehicleid||’&modDate=‘||:new.moddate||’&modTime='||:new.modtime into wm_url
FROM dual; )

in the flow I have put in a debug log step that displays these three values. Later I use these values as input for a query.

The problem is that when the trigger kicks of the job the query does not produce any results. However if I step through, or run the flow through the developer - or even run it through the browser it works. I can see from the logs that the values are passed, but the query does not work when kicked off by the trigger.

does anyone have suggestions on how to de-bug this or know what is going on here. I seem to have hit a wall

thanks in advance

–Steve

here is the log from the job from the trigger:
[392]
[391]%wo/changeByDate%
[390]TRAPEZE
[389]%wo/eqNum%
[388]%wo/description%
[387]%wo/statusDate%
[386]WAPPR
[385]2005-04-29 08:45:08 GMT+05:00 [ISP.0090.0004C] TrapezeToMMMS.Trans:Main – New Vehicle Maintenance Record: 1057
[384]2005-04-29 08:45:08 GMT+05:00 [ADA.0001.0101D] Connected to database on “p650b1” with “LOCAL_TRANSACTION”.
[383]2005-04-29 08:45:08 GMT+05:00 [ADA.0001.0101D] Connected to database on “170.121.1.34” with “LOCAL_TRANSACTION”.
[382]2005-04-29 08:45:08 GMT+05:00 [ISP.0090.0004C] TrapezeToMMMS.Trans:Main – New Vehicle Maintenance Record: 12013 - 20050429 - 35248

and one from [url=“http://170.121.201.18:5555/invoke/TrapezeToMMMS.Trans:Main?vehicleID=12013&modDate=20050429&modTime=35248”]http://170.121.201.18:5555/invoke/TrapezeToMMMS.Trans:Main?vehicleID=12013&modDate=20050429&modTime=35248[/url]:
[403]
[402]04/29/2005 09:49:33
[401]TRAPEZE
[400]B2013
[399]TESTING3
[398]04/29/2005 09:49:33
[397]WAPPR
[396]2005-04-29 08:49:37 GMT+05:00 [ISP.0090.0004C] TrapezeToMMMS.Trans:Main – New Vehicle Maintenance Record: 1057
[395]2005-04-29 08:49:37 GMT+05:00 [ADA.0001.0101D] Connected to database on “p650b1” with “LOCAL_TRANSACTION”.
[394]2005-04-29 08:49:37 GMT+05:00 [ADA.0001.0101D] Connected to database on “170.121.1.34” with “LOCAL_TRANSACTION”.
[393]2005-04-29 08:49:37 GMT+05:00 [ISP.0090.0004C] TrapezeToMMMS.Trans:Main – New Vehicle Maintenance Record: 12013 - 20050429 - 35248

Lines 382 and 393 echo back the valuse pased in the invoke. Lines 387,388,389,391 & 398,399,400,402 are the results from the query

Just a shot in the dark. Could it be that the DB transaction isn’t committed yet? It probably works when stepping through Developer because the control of process flow is different–trigger invoked service actually returns control to DB during stepping (pure speculation on my part).

Perhaps the service that is invoked by the trigger could kick off it’s processing asynchronously–spawn a new thread, giving the DB a chance to commit.

But like I mentioned, this is just a complete shot in the dark.

On a side note, I assume some application or other process is writing to the DB that fires the DB trigger. What will happen is IS is not available when the trigger fails? Will it rollback? Will it commit? Will you miss a document that you don’t want to miss?

Thank you for your reply.

I have been toying with that. I’ve tried turning the adapter service to NO_TRANSACTION. and I’ve also used the pub.art.transaction:startTransaction
[database query]
pub.art.transaction:commitTransaction
With LOCAL_TRANSACTION

Everything I’ve run comes up with the same results.

The process to handle a missed trigger event if the IS happens to go down is a good question - since this is in the early development phase we should look at our options. Probably a log or a queue

Just to make sure I understand your process:

1. Some application or process writes something to a database. (Is this an IS-hosted process?)

2. A trigger is configured to fire on insert and/or update. The trigger does an http post to an IS instance.

3. The IS service does some work, including trying to read the record that just go written to the DB. This read is failing. You’ve tried NO_TRANSACTION setting on this to no avail.

My thinking is that the transaction in step 1, not step 3, is not yet committed and so no process anywhere will be able to select the record. That original transaction won’t commit until the trigger completes, which won’t happen until the IS service returns.

If I’ve described your process accurately, would strongly advise against having the DB trigger invoke an IS service directly. Would be better to use the notification techniques of the JDBC Adapter, in which a DB trigger writes to a buffer table and then the adapter polls that table for new records.

That makes sense. After trying a few other things it dawned on me that application’s insert was the one not committing. But I was still uncertain when the commit actually happens - now I know.

I’ll try your suggestion to poll on a buffer table.

Thanks for your help.