Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Column update based on expiration criteria Expand / Collapse
Author
Message
Posted Friday, March 15, 2013 1:52 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 10:28 AM
Points: 1,153, Visits: 541
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
Post #1431727
Posted Friday, March 15, 2013 1:58 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:07 PM
Points: 22,532, Visits: 30,309
Run it as a scheduled job would be my suggestion.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1431731
Posted Friday, March 15, 2013 2:00 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:55 PM
Points: 41,570, Visits: 34,495
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1431734
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse