0

heyy... i need to knw how to update some table value to default value automatically after some period of time. assume that user update table value to "false" today. then it will be update to its default value "true" to tommorow by automatically. some one told me this can be done using "Trigger". bt i dont know how to use Trigger even.. please help...

2
Contributors
1
Reply
2
Views
7 Years
Discussion Span
Last Post by griswolf
0

A trigger cannot directly do what you want. The documentation says: A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table. The trigger becomes associated with the table which must be a permanent table. CREATE TRIGGER was added in MySQL 5.0.2.
What you want is a cron script that runs periodically and checks the table for the 'young' values associated with a row-creation timestamp and modifies the ones for which the timestamp is older than now() minus the interval:

create table true_in_interval (
  /*whatever other data*/
  defaulted_value boolean default true,
  last_touched datetime
);

/* run this update periodically */
  
UPDATE true_in_interval set defaulted_value = false WHERE defaulted_value = true and last_touched < (now() - interval 1 day);

where there is no guarantee that this is precisely what will work for you, nor that the syntax is perfectly correct.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.