5 Replies - 806 Views - Last Post: 21 April 2015 - 12:59 PM Rate Topic: -----

#1 dq1406   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 27
  • Joined: 18-March 13

question on stored procedure delete command

Posted 21 April 2015 - 10:23 AM

hello

I am doing my graduate project and working on SQL server 2012 and visual studio 2013

in store procedure I put three basic command insert , update and delete

but my teacher said the big wrong is delete there should be no delete data from database cuz maybe when I need it or
someone delete it I can put it back !! she said when I delete users or video I should only make it not available ?

and to be honest I don't know how !!

I searched in sql command there is no other than delete >_<

please help me as fast as you can I need a help and I will be very thankful :(

Is This A Good Question/Topic? 0
  • +

Replies To: question on stored procedure delete command

#2 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 462
  • View blog
  • Posts: 2,204
  • Joined: 07-April 08

Re: question on stored procedure delete command

Posted 21 April 2015 - 10:27 AM

What your teacher is talking about is a very common practice. They are basically saying that you need to always keep a record of data added no matter if someone "Deleted" the data or not.

To accomplish this you would create a column called IsDeleted that is of type bit. Then in your DELETE stored procedure you would UPDATE that flag to 1(true). In your SELECT Stored procedure you would then put a where condition that says WHERE IsDeleted = 0(false).

Now, I am concerned because you said you have a single stored procedure. That is not good design. Your procedures should do one and only one thing: Update,Delete,Insert, or Select. This keeps separation of concerns alive, as well as makes it so that you are not passing in "Flags", and your Sql Server can create a query plan which will help in performance.

This post has been edited by rgfirefly24: 21 April 2015 - 10:30 AM

Was This Post Helpful? 1
  • +
  • -

#3 dq1406   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 27
  • Joined: 18-March 13

Re: question on stored procedure delete command

Posted 21 April 2015 - 10:54 AM

first thank you so much for fast replay

second let me tell you what I understand

in each table I created which are member , video and comment I should add column flag int return 1 or 0 true or false

and in store producer I put is delete command from 0 to 1 ?

so lets say 1 mean true delete in asp.net log in code I should check the database if its true then tell the user his

account is deleted ? right
Was This Post Helpful? 0
  • +
  • -

#4 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 462
  • View blog
  • Posts: 2,204
  • Joined: 07-April 08

Re: question on stored procedure delete command

Posted 21 April 2015 - 11:54 AM

View Postdq1406, on 21 April 2015 - 12:54 PM, said:

in each table I created which are member , video and comment I should add column flag int return 1 or 0 true or false


Not exactly. It will be a BIT column, and typically people call it IsDeleted. You would set this column to either 1 or 0 depending on if the row of data it pertains to was "Deleted" by the user or not. I would also typically set the default for this column to 0 so that unless it's been specifically set it will always default to not deleted.

Quote

and in store producer I put is delete command from 0 to 1 ?


You would not use a delete command, you are UPDATEing the column from a 0 to a 1 when the user clicks the delete button in the asp.net form. (Hint: You will use an UPDATE command)

Quote

so lets say 1 mean true delete in asp.net log in code I should check the database if its true then tell the user his

account is deleted ? right


that's up to you how you handle when someone's account has been deleted.

This post has been edited by rgfirefly24: 21 April 2015 - 11:55 AM

Was This Post Helpful? 1
  • +
  • -

#5 dq1406   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 27
  • Joined: 18-March 13

Re: question on stored procedure delete command

Posted 21 April 2015 - 12:14 PM

great I understand now :clap:

thank you very much :tt1: :cry2:

it was clear as the sun

hope you have a good day and thanks one more time
Was This Post Helpful? 0
  • +
  • -

#6 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 462
  • View blog
  • Posts: 2,204
  • Joined: 07-April 08

Re: question on stored procedure delete command

Posted 21 April 2015 - 12:59 PM

So I wanted to give you a what if situation on how I would handle the login page. So a user registers with presumably a name/email/username/password. So let's say User A creates an account: Bob Jones, [email protected], BobTheBlob, password.

Now let's say Bob decides he's done with the website and wants to terminate his account. He clicks the Delete Account button in his profile page. This logs him out and gives him conformation his account is no longer active. Behind the scenes though, the record of his account is still there, just it's IsDeleted flag was set to 1. Now if bob comes back later and attempts to log in, the Login code will check the database to see if any records match his login information where the IsDeleted flag = 0, and because he deleted his account it will returned no such user exists.

Now let's say that he decides that he wants to come back to the site. You have two choices at this point.

1. If there is an account already that matches his registration information, re-activate it by setting the IsDeleted flag to 0, and resetting the Registration Date to null as well so he has to go back through the confirmation logic.

2. Set the uniqueness of the table to include the IsDeleted flag, and every time someone creates an account that matches the email/username/etc, just create a new record and ignore the one that is already there. As long as the previous one has an IsDeleted flag of 1 it will pass the Unique constraint of the table. The issue here lies in if he deletes his account again. Now the Unique constraint will fail because two records with the same information now have an IsDeleted flag of 1.

As you can see, method one is the more ideal one as it doesn't introduce even more hurdles, plus it allows a user to pick up where they left off if they decide to come back again.

Now what happens if a new person wants the same username? You again have two choices.
1. As long as Bob has deleted his account, that username is available to be used. So, when the new user registers with BobtheBlob, a new record will be inserted because the email addresses will be different. So then what happens when Bob wants to come back? Well at that point he will need to register with a new username, and you will either have to put in logic to merge his old account to his new one or ignore the old one and not allow him to retrieve old records.

2. Not allow the new person to use that username as it's not technically gone, so if the user who first had it wants to return they can still pickup where they left off.

My choice would be choice 1, with the ability to merge the old account based on the email address.

Now I'm not saying that What I would do is the absolute best way to handle it, just my opinion on how it should work. These are things you will need to think of when you are building outward facing products that give the ability to soft delete data.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1