I run several jobs on Windows scheduler. Instead of a time trigger, is it possible to trigger jobs on table inserted (excluding db triggers)?
I assume you mean that you want to start a job/route by detecting the entry of a record in the table. You can use the Camel SQL component as a polling consumer, which will periodically check the table for records. See details in the link below.
If you are not marking the records as processed in some way, then you would need to implement the idempotent version which requires defining an additional table for marking the records the system has already seen.
The polling consumer can be set up to poll at whatever interval you need, but obviously can't be triggered directly by the database change. However, if the table you are monitoring is small, or suitably indexed (to determine unprocessed rows) then this shouldn't be a problem.
Once you have fetched your triggering data, you can then send it to a talend job with the cTalendJob component.
Thanks for your answer, my main condition was no timed triggers. I'm hoping to find a solution for immediate on insert triggers/alerts.
I'm able to do what you suggested by recording the last db_id returned and resuming from there with windows scheduler.
Given that you have full control of the database, then I'd suggest one of two options:
Firstly, create a table that will reference the records that you need to hold. If you don't need the "previous" value, I'd use a generic table which holds the table name and row id. You then build a trigger (on insert/update or whatever you need) that inserts records into this table.
Using Camel's SQL component, you can monitor this table (every minute/second/etc), and process the records referred to, and deleting the reference on completion.
This approach has the advantage of transactional integrity - you will only see the reference record once the actual change has been committed.
The more direct approach would be to write a trigger that calls a stored procedure that triggers your job via a restful service. You would need to include all the data in the service call, because if you query the database, the change hasn't yet been committed, unless you put some sort of delay process in place. The other downside of this is you've triggered your job before the transaction has committed, so it may not be committed if something goes wrong later.
As such, unless having a polling SQL consumer is really not possible, I'd use the former approach.
Not the solution I'm looking for still involves triggers and is not fully automated since it needs monitoring
but I will kudos you for trying and possibly comming up with a solution others can use.
Could you please let us know if this related topic helps?
Watch the recorded webinar!
Create systems and workflow to manage clean data ingestion and data transformation.
Introduction to Talend Open Studio for Data Integration.
Test drive Talend's enterprise products.