AndroidDev AndroidDev -4 years ago 77
MySQL Question

Deleting a row when it remains one month in the database

In my application, I want the user to be able to retrieve deleted information within one month.

For example:

Create Table 'Names' (name varchar(20) primary key)

Create Table 'DeletedNames'
(
name varchar(20) primary key,
insertingTime DateTime not null
)


When I delete a row from the
Names
table, it is inserted into the
DeletedNames
table.

For example:

Deleting name
Mohammed
, it will be inserted into
DeletedNames
:

| Mohammed | | 2017-6-11 10:00 PM |


I want to delete this after one month 2017-7-11 10:00 PM (automatically)

I haven't tried anything. Maybe trigger would be a solution, but I'm beginner in triggers.

Thank you

Answer Source

You don't need a trigger. Just set up an agent job with the delete statement. Schedule it to run every day.

DELETE FROM DeletedNames WHERE InsertingTime < DATEADD(day,-30,GETDATE())

In MySQL use the Event Scheduler.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download