Auto deletion of table in database after certain period of time

[PreethiGowri]

I want to auto delete a particular table after a month,
suppose i have a table 'raj' which contains all the details of what he purchased at a shop today(30/10/2012) i want this table to be auto deleted exactly after a month(30/11/2012),
how do i do this??

[smartchap]

In the table itself make a field for Date Created and in the program check the value (Date) of this field for each table in the database, if it is more than or equal to one month delete that particular table.

[PreethiGowri]

Are you talking about this query?
"DELETE FROM table WHERE now() > expiry";
expiry = 1 month
This query truncates the table but does not drop the table from database

[Rabbit]

Why would you need to drop the table? It's odd to be dropping tables all the time.

[PreethiGowri]

the table i want to drop is a temporary one

[Rabbit]

But what is the purpose of the temp table?

[PreethiGowri]

i'm working on a shopping based database, here i'm suppose to create a table with the customers name(a temporary table) which has to be maintained for a month as a backup, later this as to be auto deleted

[Rabbit]

It doesn't make sense to create a table for each customer. Rather, you should have an order table with all customers in there. And if you need to delete/archive the data for a certain customer after a certain amount of time has elapsed, you can do that. But it's bad design to create a temporary table for every customer and drop them constantly. It just makes querying and reporting a mess.

[PreethiGowri]

i was working the concept you told

[Rabbit]

What concept are you referring to? I never told you to create a temp table for each customer.

[PreethiGowri]

I was working on event schedulers

[Rabbit]

I'm not sure what that has to do with this thread.

[PreethiGowri]

using that we can perform a auto deletion operation