Create Job to add and remove data automatically.

Question

Create Job to add and remove data automatically.

aislancezar

SSC Enthusiast

Points: 182
More actions
November 21, 2010 at 9:06 pm

#226906

Guys,
Good morning everyone!
Good staff, to needing a little help from you.
I have two tables that need to add and remove data automatically.
example:
'm Doing at the moment as follows.
A user makes a donation of a certain sum of money and get a VIP account on my site and will have certain rights for 30 days.
Alas, I have to go manually in sql server 2008 r2 standard and add the data manually and when they complete these 30 days, I have to go back and delete the data.
Example:
Name of the 1st table: user_dir
Columns: user_id, nome_user, nick_user, level;
Actions: suppose there is already a registered user on my site: user_id = '1 ', name_user =' Charles ', nick_user =' Carlito ', level = '1';
So I intend to add the prefix [VIP] nick_user the number 30 and the level of the user eg nick_user = '[VIP] Carlito', level = '30 ';
name of the 2nd table: user_direitos
Columns: user_id, direito_id, direito_nivel;
Action: Add the following data tables: user_id = '1 ', direito_id = '8', direito_nivel = '30 ';
Then, after having passed the 30 days, something that would automatically remove these data from two tables mentioned above, and the user back to being a normal user.
Is it possible? Is there a way, or something that removes the data automatically once the 30 days being completed?
Now, thank you everyone's attention. =)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mike John SSCertifiable Points: 7216 More actions · Answer 1

You could schedule a job inSQL Server agent to delete rows from eithe table base don any condition you choose. But I see no column in your description that would allow the code to know that 30 days have elapsed. Something like a "date_created" or "date_subscribed" column would seem to be required.

How do you currently know who to delete?

Now once there is a column that says when they signed up you could easily write something that deleted everybody where the subscribed_date + 30 days is > current_timestamp, but in my view knowing that they were signed up at some point probably has value, so rather than deleting them just treating them as "expired" once the 30 days is up would make sense.

Re-reading your post I just realised you are manually INSERTING the new sign ups as well? So is the web site not storing data in SQL Server?

If you can fill in more details somebody may be able to help more. Full DDL of all the tables involved would laos be a good thing so everyone has the complete picture.

Mike

aislancezar SSC Enthusiast Points: 182 More actions · Answer 2

Mike John (11/22/2010)You could schedule a job inSQL Server agent to delete rows from eithe table base don any condition you choose. But I see no column in your description that would allow the code to know that 30 days have elapsed. Something like a "date_created" or "date_subscribed" column would seem to be required.

In this case, I could create a column with a date on which the privilege was added to the user, without causing any trouble to the table?

example:

Columns: user_id, direito_id, direito_nivel, data;

Does the date field, would create a Job?

How do you currently know who to delete?

Currently, I write down in a notebook the date of creation, then when you complete the 30 days I go there and delete.

Now once there is a column that says when they signed up you could easily write something that deleted everybody where the subscribed_date + 30 days is > current_timestamp, but in my view knowing that they were signed up at some point probably has value, so rather than deleting them just treating them as "expired" once the 30 days is up would make sense.

So let's say I add a column called date and you want to delete data from the tables reported in the post, How would the Job? Or how would the code?

If you can fill in more details somebody may be able to help more. Full DDL of all the tables involved would laos be a good thing so everyone has the complete picture.

In this case, the tables that I use to give the right users are only these same two user_dados and user_direitos

example:

Name of the 1st table: user_dados

Columns: user_id, nome_user, nick_user, nivel;

scrip: suppose that there is already a registered user on my site: user_id='1', name_user='carlos', nick_user='carlito', nivel='1';

So I intend to add the prefix [VIP] in nick_user and the number 30 in nivel User, example: nick_user='[VIP]carlito', nivel='30';

Name of 2nd table: user_direitos

Columns: user_id, direito_id, direito_nivel;

scrip: Add the following data tables: user_id='1', direito_id='8', direito_nivel='30';

Already, thanks.

SQLfan75 SSC Enthusiast Points: 160 More actions · Answer 3

I would add 2 date fields, start_dt and end_dt and populate both when the record is added. Then change all sql selecting data to use getdate() between start_dt and end_dt. This way you can keep a record of all past users and you don't ever need to "delete after 30 days".