I have a table, tblClient, which stored a client's date of birth in a field of type datetime, DOB.
The goal here is that, when a client reaches 65 years old (need to be calculated thru DOB), I need to insert a new record into another table.
But since the age of a client does not change due to a database transaction (INSERT,UPDATE,DELETE), trigger is out of question.
What would be a good idea to montior such changes?
create a sql agent job that runs daily or hourly that will do this calculation with T-SQL and then if someone reaches 65 it will do the insert
A scheduled task or SQL Server maintenance plan that runs a stored procedure as often as required, updating the required rows.
What about a nightly job using SSIS with a stored procedure that checks and if it happens that they are 65 it enters a new row in the table?
Keep it as self-contained to SQL Server as possible - a SQL Server Agent job that periodically executes a stored procedure should do nicely.
you can create a SQL Server Agent Job within the database using SQL Server Management Studio for this:
http://www.databasedesign-resource.com/sql-server-jobs.html
Set up a daily job to EXEC BirthdayProcessingProcedure or whatever you want to name it.
As long as the database is up and running, the JOB will run according to the schedule you set up (from within the database).
I'm going to propose another approach - run something every time a DOB is updated (or added) that calculates the period from now until the first person reaches 65. Then (re-)schedule a job to run at that time.
Also, I can't believe you need to insert that row the second they reach 65, so a once-a-day procedure that calculates today's new 65year olds would seem good enough?
How about a new field that is age65 date. Calculate it once on record insert, then you can query to your hearts content on this field. You will need to do this is a trigger (and account for updates, they are rare for DOB fields but possible when they are mistyped.) Now that I think about it some, a calculted filed will probably work instead of a trigger.
Then run a daily job to catch anyone who turned 65 since the last time the job was run successfully. Make sure to handle this so that if the job fails one day, the people from that daty are picked up the next run.
The reason why I suggest this is that calculating the age of every person inyour database every day is such a waste of resources for a calculation that really only needs to be done once. Ok not a big deal when you have 100 people, big problem when you have a million. Doindthis kindof calc on a million records to identify the three you need is painful. Doing it once on data entry, not so bad.