SQL Server - New/Updated row based on view

[mlamotte]

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 🙂

[mlamotte · Accepted solution]

Solved

[mlamotte · Accepted solution]

Solved

[meghan-legaspi]

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

[mlamotte · Accepted solution]

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.