CODEWITHSUNDEEP
phpmysqlsqltriggerseventtrigger
DominicS
DominicS

Reputation: 23

SQL TRIGGER EVENT FOR PHP

I'm developing with PHP + MySQL.

I need to delete a row (tuple) in a table when the expiration date occurs.

Probably the best way be a Trigger but I have no idea how to do it.

Somebody have an example?

Upvotes: 1

Views: 1566

Answers (1)

VMai
VMai

Reputation: 10336

You could use MySQLs event scheduler. The minimal example of the manual page of CREATE EVENT would be a good base for that what you probably need:

 CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    DO
      UPDATE myschema.mytable SET mycol = mycol + 1;

If you want to clean up every 10 minutes you would use 

CREATE EVENT myevent
    ON SCHEDULE EVERY 10 MINUTE

And your clearing statement would be a DELETE:

    DO
      DELETE FROM yourTable WHERE expiration_date < NOW();

That's all and MySQL will do it automatically every 10 minutes. Complete in four lines of SQL:

CREATE EVENT clear_expirations             -- a better name for the event
    ON SCHEDULE EVERY 10 MINUTE         -- as you need it
    DO
      DELETE FROM yourTable WHERE expiration_date < NOW();

Edit

Per default the event scheduler will be stopped:

The global event_scheduler system variable determines whether the Event Scheduler is enabled and running on the server. It has one of these 3 values, which affect event scheduling as described here:

OFF: The Event Scheduler is stopped. The event scheduler thread does not run, is not shown in the output of SHOW PROCESSLIST, and no scheduled events are executed. OFF is the default value for event_scheduler.

When the Event Scheduler is stopped (event_scheduler is OFF), it can be started by setting the value of event_scheduler to ON. (See next item.)

ON: The Event Scheduler is started; the event scheduler thread runs and executes all scheduled events.

...

DISABLED: This value renders the Event Scheduler nonoperational. When the Event Scheduler is DISABLED, the event scheduler thread does not run (and so does not appear in the output of SHOW PROCESSLIST). In addition, the Event Scheduler state cannot be changed at runtime.

You can get the value of this system variable with

SELECT @@event_scheduler;

If the result is OFF you can start the event scheduler with

SET GLOBAL event_scheduler = 'ON';

You should modify the configuration of your MySQL server, so that the event scheduler will be running at the start of the mysqld process.

It should be clear, that you got to be administrator of your MySQL server to modify this configuration.

Upvotes: 1

Related Questions