March 15, 2013 at 1:52 pm
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.
SELECT
T1.SerialNumber,
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
FROM
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.
Thanks
March 15, 2013 at 1:58 pm
Run it as a scheduled job would be my suggestion.
March 15, 2013 at 2:00 pm
That'll be a scheduled job to run on whatever interval you want and update the relevant rows. You're right, this is not for a trigger. A trigger is for when some other data should change when a user makes some changes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply