Triggering job on table insert

Six Stars

Triggering job on table insert

I run several jobs on Windows scheduler. Instead of a time trigger, is it possible to trigger jobs on table inserted (excluding db triggers)?

Six Stars

Re: Triggering job on table insert

Hi!

 

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.

 

https://camel.apache.org/sql-component.html

 

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.

Six Stars

Re: Triggering job on table insert

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.

Six Stars

Re: Triggering job on table insert

What is the database you are using? Do you have DBA privileges?
Six Stars

Re: Triggering job on table insert

MSSQL Full admin

Six Stars

Re: Triggering job on table insert

PS emails are no good

Six Stars

Re: Triggering job on table insert

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.

Six Stars

Re: Triggering job on table insert

Thanks Screwtape

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. 

Moderator

Re: Triggering job on table insert

Hello,

Could you please let us know if this related topic helps?

https://community.talend.com/t5/Design-and-Development/How-to-trigger-a-Talend-job-based-on-the-mysq...

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.