SQL Trigger - VisualCron - Forum

Community forum

osirisja
2012-12-17T11:22:50Z
Hi Support

Being unfamiliar with SQL triggers in general, but understanding how useful they can be, I was wondering if there is any documentation or example set ups to explain how the SQL Trigger works?

For example, I want to run a VC Job if a new Row is created in a Sales table. Do I just query the content of the table and if there is a new entry the trigger will know?

Apologies if this is a Dumb question.........

Cheers

Andy
Support
2012-12-17T13:59:56Z
Hi Andy,

you can find Task documentation if you download lastest and open documentation.

But maybe your question was more in general. The idea with the SQL Trigger is that you design a query that returns a result you expect.

In your case it depends on how the table look at how you know when a new row has been processed. In your case you could return the Id of the TOP row sorting by id or date descending. Then you can use that in combination with "Do not Trigger if value is the same".

There are a lot of ways to do this depending on how your table looks like.
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
cupcakeman82
2021-05-10T20:24:16Z
Originally Posted by: Support 

Hi Andy,

you can find Task documentation if you download lastest and open documentation.

But maybe your question was more in general. The idea with the SQL Trigger is that you design a query that returns a result you expect.

In your case it depends on how the table look at how you know when a new row has been processed. In your case you could return the Id of the TOP row sorting by id or date descending. Then you can use that in combination with "Do not Trigger if value is the same".

There are a lot of ways to do this depending on how your table looks like.




But nowhere is written HOW to do the above, the documentation doesn't give any visual concrete examples of that sort

For example, HOW to store a value of a sql query result? what if I want to store ONLY the id of the top record for example in some variable?

Let's say I am querying:
SELECT * FROM db.components ORDER BY id DESC; and I want to get the value of top id only, and compare it; there's nothing in the "conditions" tab that let me compare the value of some cell in query result (ie. id of top row) with a preset value, how to achieve this?

image.png
Screenshot_2.png
thomas
2021-05-11T08:27:53Z
I think the idea behind the trigger is that you return only one record. In your case that would be
SELECT TOP(1) id FROM db.components ORDER BY id DESC

If you need more data for the rest of the job I would fetch that later. The query in the trigger should be as light as possible as it is executed repeatedly, it shouldn't be SELECT * from Table. If something new has happened, the rest of the job is exectuted and you can go and fetch the data you need for your business logic.
cupcakeman82
2021-05-11T18:35:54Z
Originally Posted by: thomas 

I think the idea behind the trigger is that you return only one record. In your case that would be
SELECT TOP(1) id FROM db.components ORDER BY id DESC

If you need more data for the rest of the job I would fetch that later. The query in the trigger should be as light as possible as it is executed repeatedly, it shouldn't be SELECT * from Table. If something new has happened, the rest of the job is exectuted and you can go and fetch the data you need for your business logic.



Forget about the query, what to put exactly in the CONDITION tab?

Let's take your query as example (which is logical), how to put the value of the query result in a variable in order to use for compare it in the *condition tab* ?
Casper
2021-05-11T22:12:46Z
Originally Posted by: cupcakeman82 

Originally Posted by: thomas 

I think the idea behind the trigger is that you return only one record. In your case that would be
SELECT TOP(1) id FROM db.components ORDER BY id DESC

If you need more data for the rest of the job I would fetch that later. The query in the trigger should be as light as possible as it is executed repeatedly, it shouldn't be SELECT * from Table. If something new has happened, the rest of the job is exectuted and you can go and fetch the data you need for your business logic.



Forget about the query, what to put exactly in the CONDITION tab?

Let's take your query as example (which is logical), how to put the value of the query result in a variable in order to use for compare it in the *condition tab* ?



Hi, in your case you can create SQL task as last task in your job with same query. And then in trigger condition tab compare with this task output, if values is different, trigger will fired.
thomas
2021-05-12T10:54:19Z
The value from the query is implicit. It is stored behind the scenes. In the condition tab you put in the value you want to compare it with, not the result from the query
Scroll to Top