Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Could someone please show or describe to me how to implement soft deletes?

I have table of vouchers with these fields: id, username, password, serial.

I want to display a given number of rows depending on the customer's request and once displayed they should be deleted so that the are not displayed again. (They are already invalid.)

I presume soft delete will ensure that no voucher is displayed twice. If you have any other idea on how I would do it I would appreciate.

share|improve this question
add comment

2 Answers

I have always implemented a Soft Delete by including two additional columns in my tables, one for status and one for delete date.

My table structure would be similar to the following:

create table myTable
(
  id int,
  name varchar(50),
  IsDeleted bit,
  DeletedDate datetime
);

The IsDeleted column has a default value of no and the DeletedDate is not populated until the row is actually marked as deleted. I include a date column in the event I ever want to know when a row was soft deleted.

Then when you query your data, you will include this column in the WHERE clause:

select id, name
from myTable
where IsDeleted = 0

Note: You will want to include an index on the IsDeleted column.

share|improve this answer
1  
the idea is very fine and seems what i really want, except that am not sure about one thing, how to execute two queries at once, and how do i determine which row to mark as deleted –  Ruyonga Dan Mar 13 '13 at 17:46
    
@RuyongaDan Unfortunately, I don't see an easy way to do it with a SELECT. You will have to use a stored procedure that can perform multiple operations. If it was my process, I would look at using a temp table to place the data in that can be used to update the main table and then return the rows to the user. –  bluefeet Mar 13 '13 at 18:36
    
thankx @bluefeet let me try to figure it out –  Ruyonga Dan Mar 14 '13 at 10:43
add comment

The answer given by @bluefeet is good enough to help you.

Soft delete's are common practice .Please have a look at some of the urls that will suggest you the pros and cons of soft deletes and some other great info.

http://stackoverflow.com/questions/2549839/are-soft-deletes-a-good-idea

http://stackoverflow.com/questions/378331/physical-or-logical-delete-of-database-record

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.