CODEWITHSUNDEEP
mysqlsqltriggers
user3423384
user3423384

Reputation: 707

Trigger that delete row if currentdatetime > datetime+5

I'm creating a database where i want to create a trigger.

This trigger will delete a row if the current time is 5 min over the requesttime in the table.

The requesttime is a datetime attribute in the table.

How can i make a trigger which do this?

Upvotes: 0

Views: 1463

Answers (2)

Ithar
Ithar

Reputation: 5465

You may want to consider Event Scheduler added in MySQL 5.1.6. Events can be seen as an alternative to Cronjob.

Their main advantages are:

  1. Platform independent; directly written in MySsql
  2. Ability to list all events SELECT * FROM INFORMATION_SCHEMA.EVENTS;
  3. Built in error logging option

Syntax is something similar to:

CREATE EVENT my_event
ON SCHEDULE 
    EVERY 1 MINUTE
    STARTS '2015-01-01 00:15:00'
COMMENT 'Removes forgotten password tokens older thank 1 week.'    
DO
    DELETE FROM my_table WHERE some_timestamp > NOW();

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270523

You don't want to do this. First, triggers only run when something is happening inside the database (such as an insert or update). They are not the same as scheduled jobs. Second, you generally don't want to do this in a scheduled job. Instead, just create a view:

create view v_table as
    select t.*
    from table t
    where requesttime >= now() - interval 5 minute;

This will return valid requests.

Then, at your leisure, delete the old rows -- if you really need to. There is a good chance you might want to keep them around to know what happened in the past. But you can delete them on Sunday morning at 3:00 a.m. or some other slow time, if you want.

Upvotes: 1

Related Questions