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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy