topic Re: SQL Server - New/Updated row based on view in Workato Pros Discussion Board

SQL Server - New/Updated row based on view

mlamotte — Fri, 07 Jun 2024 09:48:02 GMT

Hi,

I have a view in SQL that shows a schedule (production order) with its startdate and update date (the update date does not always mean that the production order has started, just that something has been updated).

What I want to do is find a schedule that has started, so where startdate is updated from NULL to an actual date.

I was trying to not use a WHERE condition (e.g. WHERE stardate >= DATEADD(minute,-1,getdate()) to see which schedule for example started in the past minute, because there might be some lag on the update of the field.

How could I set up this action that it is just triggered by value startdate going from NULL to NOT NULL? Use a trigger condition or would a WHERE condition be possible?

I also want to avoid duplicate triggers, that we don't send the same information twice.

EDIT:My bad, my query was the issue. IS NOT NULL was enough, and it works now 🙂

Re: SQL Server - New/Updated row based on view

mlamotte — Fri, 07 Jun 2024 09:48:36 GMT

Solved

Re: SQL Server - New/Updated row based on view

meghan-legaspi — Mon, 10 Jun 2024 15:20:05 GMT

Hey @mlamotte – what was the solution that worked for you? I'm sure other community members here would love to know!

Re: SQL Server - New/Updated row based on view

mlamotte — Tue, 11 Jun 2024 08:19:11 GMT

My query selects all schedules that have a DTSStart filled in, so I already filter out the NULL values in my query.

The DTSStart date is the sort column I use.

The first few times the recipe was triggered based on all items in the query that have a start date filled in. But as soon as it has "handled" all old items, it stopped getting triggered by those, and is actually only getting triggered by new updates.
I was confused because I had 5 rows from the query that would trigger, but having a bit of patience solved this.