user3423384 user3423384 - 9 months ago 25
SQL Question

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?


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.