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

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.

---

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?

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.

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* ?

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.

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