oracle year change trigger

I m on a problem that I can t figure out. I m building an application in c++ builder 2009 and oracle 11g. I have some calculated data that depend on users age. What I want to do is to re-calculate these data every new year. I thought I could have a trigger to do this, but I don t know which event I should catch and I didn t find something in internet.

My table is :

ATHLETE (name, ......, birthdate, Max_heart_frequency)

Max_heart开发者_C百科_frequency is the field that depends on age. In insertion I calculate athlete's age, but what about next year?????? Can anyone help????

How is the max_heart_frequence calculated?

If this is a simply formula, I would create a view that returns that information. No need to store values that can easily be calculated:

CREATE VIEW v_athlete
AS 
select name, 
       case  
          -- younger than 20 years
          when (MONTHS_BETWEEN(sysdate, birthday) / 12) < 20 then 180

          -- younger than 40 years
          when (MONTHS_BETWEEN(sysdate, birthday) / 12) < 40 then 160

          -- younger than 60 years
          when (MONTHS_BETWEEN(sysdate, birthday) / 12) < 60 then 140

          -- everyone else
          else 120
       end as max_heart_frequency
from athlete

Then you only need to select from the view and it will always be accurate.

You can use oracle scheduler to run a procedure at specific intervals (can be minutes hours, daily, yearly etc .. any time span).

Check this linke: http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/schedover.htm

You have two options:

1. Have a stored procedure that calculates and updates the Max_Heart_Frequency of all the athletes every 01st Jan (using the yearly scheduling of a procedure)

2. Have a stored procedure that runs daily and calculates and updates the Max_Heart_Frequency of all the athletes every day (using the daily scheduling of a procedure)

If Max_Heart_Frequency changes over time because the user is getting older, why are you storing it in the table in the first place? Why not just call the function that computes the maximum heart rate at runtime when you need the value? Potentially, it may make sense to have a view on top of the Athlete table that adds the computed Max_Heart_Frequency column to hide from the callers that this is a computed column.