CODEWITHSUNDEEP
mysqlsqldatetimetriggerssql-delete
carlofacose
carlofacose

Reputation: 35

Create a trigger that deletes data from 14 days ago

I need to create a trigger that allows me to delete 14 days old data. I know how to create the DELETE query, but I need to find a way to declare a variable with the current date - 14 days ago. Important, the date I have is a time-stamp ... yyyy-mm-dd hh: ss: ms !!

La struttura della tabella è la seguente:

ID --> type:Int Primary Key
Name --> type:Varchar
Surname --> type:Varchar
registration_date --> type: TimeStamp

Someone can help me?? Thank You!!

Upvotes: 0

Views: 1079

Answers (2)

Akina
Akina

Reputation: 42728

CREATE EVENT delete_old_rows_from_tablename
ON SCHEDULE EVERY 1 DAY 
            STARTS CURRENT_DATE + INTERVAL 1 DAY
DO
    DELETE 
    FROM tablename
    WHERE registration_date < CURRENT_DATE - INTERVAL 14 DAY;

Do not forget to enable Event Scheduler.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270553

You don't want a trigger that deletes data from 14 days ago. A trigger is run only when the data changes. So, just use a delete statement.

You should schedule such a delete statement to run periodically, probably using an event -- but you might prefer some other scheduling mechanism.

In the meantime, you can also create a view to only see the most recent data:

create view v_t as
    select t.*
    from t
    where registration_date >= current_date - interval 14 day;

Upvotes: 1

Related Questions