I am trying to get a homegrown database to automatically update a 'IsActive' column. I have a merge statement for the update, but I have no idea how to get this statement to run when the record is suppose to go from active to inactive and vice versa.
My merge statement uses a view like this.
CASE WHEN (DATEADD(MONTH, T2.Schedule, T2.Date1) >= CAST(CURRENT_TIMESTAMP AS DATE)) THEN 1
WHEN (T2.Extension = 1 AND T2.DateExtended >= CAST(CURRENT_TIMESTAMP AS DATE)) THEN 1
WHEN (DATEADD(MONTH, T2.Schedule, T2.Date1) < CAST(CURRENT_TIMESTAMP AS DATE) AND (T2.DateExtended IS NULL OR T2.DateExtended < CAST(CURRENT_TIMESTAMP AS DATE))) THEN 0
ELSE CAST(NULL AS TINYINT) END AS DetermineIsActive,
CAST (CURRENT_TIMESTAMP AS DATE) AS CurrentTimestamp
dbo.Table1 AS T1
INNER JOIN dbo.Table2 AS T2
ON T1.SerialNumber = T2.SerialNumber
My merge statement is
MERGE dbo.Table1 T1
USING (SELECT SerialNumber, DetermineIsActive, CurrentTimestamp FROM vIsActiveUpdate) AS vT2
ON T1.SerialNumber = vT2.SerialNumber
WHEN MATCHED THEN UPDATE
SET T1.IsActive = vT2.DetermineIsActive;
This does the update I want, but I have no idea how to get it to execute when, for example T2.DateExtended < CURRENT_TIMESTAMP. I looked at triggers, but I don't think I want to do that. Everyone says not to use triggers, plus I can't figure out how the trigger would know when to execute. The criteria isn't really updating, it is expiring.
Hopefully I was able to explain this clearly enough.