Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Mannga
Yak Posting Veteran
70 Posts |
 Posted - 2003-03-14 : 05:01:28
|
| I have a table in my database with a date field inside.I would like to check the date field and if it is exactly a year ago to add a record into another table.I would like this to run automatically all the time.What is the best way to do this?I thought of using a DTS package but I am hoping there is a better way?Thanks,Gavin |
|
|
samsekar
Constraint Violating Yak Guru
437 Posts |
Posted - 2003-03-14 : 05:23:35
|
| Use TRIGGER to check for every insertion of the data. And use DATEDIFF to find the difference of the date.Sekar~~~~Success is not a destination that you ever reach. Success is the quality of your journey. |
 |
|
|
Mannga
Yak Posting Veteran
70 Posts |
Posted - 2003-03-14 : 05:56:37
|
I did think of that but the problem I have is that I want to check if an existing record is exactly a year old. If I use a trigger and for an entire day noone enters a record (over the weekend) then some records could be missed...I think  .... Or am I missing something here?Thanks for the replyGavin |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2003-03-14 : 08:31:06
|
| Best way to deal with this is to have a scheduled job running once a min, quarter hour, hour, day...looking for qualifying records that match your criteria....how often you want the scheduled job to run depends on whather or not you care down to the millisecond about 'what defines a year'...or if you can afford to wait until midnight to create the next set of '1 year old' records.Triggers only execute on database updates. also is it a good idea to have every insert/update/delete check 'the entire database' for "year old" records....even when the previous insert 2 seconds ago did exactly the same job? |
|
|
|
|
|
|
Checking data automatically and reacting