By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,064 Members | 1,388 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,064 IT Pros & Developers. It's quick & easy.

Auto deletion of table in database after certain period of time

100+
P: 126
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??
Oct 30 '12 #1
Share this Question
Share on Google+
12 Replies


smartchap
100+
P: 236
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.
Oct 30 '12 #2

100+
P: 126
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
Oct 30 '12 #3

Rabbit
Expert Mod 10K+
P: 12,366
Why would you need to drop the table? It's odd to be dropping tables all the time.
Oct 30 '12 #4

100+
P: 126
the table i want to drop is a temporary one
Oct 31 '12 #5

Rabbit
Expert Mod 10K+
P: 12,366
But what is the purpose of the temp table?
Oct 31 '12 #6

100+
P: 126
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
Oct 31 '12 #7

Rabbit
Expert Mod 10K+
P: 12,366
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.
Oct 31 '12 #8

100+
P: 126
i was working the concept you told
Nov 1 '12 #9

Rabbit
Expert Mod 10K+
P: 12,366
What concept are you referring to? I never told you to create a temp table for each customer.
Nov 1 '12 #10

100+
P: 126
I was working on event schedulers
Nov 2 '12 #11

Rabbit
Expert Mod 10K+
P: 12,366
I'm not sure what that has to do with this thread.
Nov 2 '12 #12

100+
P: 126
using that we can perform a auto deletion operation
Nov 2 '12 #13

Post your reply

Sign in to post your reply or Sign up for a free account.