Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Deleting a record from a database

Reply
Thread Tools

Deleting a record from a database

 
 
bthumber
Guest
Posts: n/a
 
      07-20-2009
I need to delete a record form a database, but I don't want to lose that
record. I was told I could set a flag, so the question is how do you do that?
 
Reply With Quote
 
 
 
 
sloan
Guest
Posts: n/a
 
      07-20-2009

Some people call this "soft deleting".

Create table dbo.Employee
{
EmployeeUUID uniqueidentifier primary key not null default newsequentialid()
,
SSN varchar(11) not null ,
IsDeleted bit default (0)
}



Then your "soft delete" command would be

Update dbo.Employee Set IsDeleted = 1 where SSN = '222-22-2222'

Something like that.

Of course when you want to get all employees...........you filter

Select EmployeeUUID, SSN from dbo.Employee e where e.IsDeleted = 0




"bthumber" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I need to delete a record form a database, but I don't want to lose that
> record. I was told I could set a flag, so the question is how do you do
> that?



 
Reply With Quote
 
 
 
 
Gregory A. Beamer
Guest
Posts: n/a
 
      07-20-2009
=?Utf-8?B?YnRodW1iZXI=?= <(E-Mail Removed)> wrote in
news:(E-Mail Removed):

> I need to delete a record form a database, but I don't want to lose
> that record. I was told I could set a flag, so the question is how do
> you do that?
>


There are two ways to set up a logical delete.

1. Add a field in the table called IsDeleted. This will be a bit type (SQL
Server) or a Boolean type (Access - hopefully you are not using Access).
The addition is:

ALTER TABLE Table1
ADD
IsDeleted bit default 0 NOT NULL
GO

You then have to alter your SQL so it respects this:

AND IsDeleted = 0

Except on the page where you can "undelete" an item, which would look for
deleted items only.

AND IsDeleted = 1

To delete, the command is like this:

UPDATE Table1
SET IsDeleted = 1
WHERE ID = @id

2. Create a trigger that archives the "deleted file" To do this, you create
a table exactly like the original table, but you call it _History.

CREATE TABLE Table1_History
(
-- Fields go here
)
GO

You can then set up a trigger:

CREATE TRIGGER trgTable1Delete ON Table1
FOR DELETE
AS

INSERT INTO Table1_History
(
-- Field names here
)
SELECT *
FROM deleted

This will completely remove the record, but will make a copy in a
"history" table, so it is a better option if you have already heavily
invested in queries against this table.

You can restore by reversing the delete (ie, deleting from history and
inserting into the original table).

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************
 
Reply With Quote
 
sloan
Guest
Posts: n/a
 
      07-20-2009
I should have followed up, but the "History" idea is alot cleaner than the
SoftDelete flag/column.

I call this "Archive" (just another term for it) and my table(s) would
look like this:


Create table dbo.Employee
{
EmployeeUUID uniqueidentifier primary key not null default newsequentialid()
,
SSN varchar(11) not null
}


Create table dbo.EmployeeArchive
{
EmployeeArchiveUUID uniqueidentifier primary key not null default
newsequentialid()
,
SSN varchar(11) not null
}


The one area you have to be careful.
If you (correctly) have put a UNIQUE CONSTRAINT on SSN (for example)....you
might get into the situation where you
1. Have a row. (Lets say SSN of '222-22-2222')
2. Delete (and "archive") the row. (So dbo.EmployeeArchive has a row with
'222-22-2222')
3. The row is re-added to the primary table. (Lets say an employee is
rehired). '222-22-2222' exists in dbo.Employee AND dbo.EmployeeArchive)
4. You try to delete again, and if you have the UNIQUE constraint on the
Archive table (on SSN)...........it will fail. (because you're trying to jam
2 rows of '222-22-2222' into dbo.EmployeeArchive)

So the thing you gotta remember...when you "Add New", you have to check and
see if you're able to resurrect an Archive(d) row!




CREATE TRIGGER trgEmployeeDelete ON dbo.Employee
FOR DELETE
AS

INSERT INTO dbo.EmployeeArchive (EmployeeArchiveUUID , SSN)
SELECT EmployeeUUID , SSN
FROM deleted




"Gregory A. Beamer" <(E-Mail Removed)> wrote in message
news:Xns9C4E64D48FA2Bgbworld@207.46.248.16...
> =?Utf-8?B?YnRodW1iZXI=?= <(E-Mail Removed)> wrote in
> news:(E-Mail Removed):
>
>> I need to delete a record form a database, but I don't want to lose
>> that record. I was told I could set a flag, so the question is how do
>> you do that?
>>

>
> There are two ways to set up a logical delete.
>
> 1. Add a field in the table called IsDeleted. This will be a bit type (SQL
> Server) or a Boolean type (Access - hopefully you are not using Access).
> The addition is:
>
> ALTER TABLE Table1
> ADD
> IsDeleted bit default 0 NOT NULL
> GO
>
> You then have to alter your SQL so it respects this:
>
> AND IsDeleted = 0
>
> Except on the page where you can "undelete" an item, which would look for
> deleted items only.
>
> AND IsDeleted = 1
>
> To delete, the command is like this:
>
> UPDATE Table1
> SET IsDeleted = 1
> WHERE ID = @id
>
> 2. Create a trigger that archives the "deleted file" To do this, you
> create
> a table exactly like the original table, but you call it _History.
>
> CREATE TABLE Table1_History
> (
> -- Fields go here
> )
> GO
>
> You can then set up a trigger:
>
> CREATE TRIGGER trgTable1Delete ON Table1
> FOR DELETE
> AS
>
> INSERT INTO Table1_History
> (
> -- Field names here
> )
> SELECT *
> FROM deleted
>
> This will completely remove the record, but will make a copy in a
> "history" table, so it is a better option if you have already heavily
> invested in queries against this table.
>
> You can restore by reversing the delete (ie, deleting from history and
> inserting into the original table).
>
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
>
> Twitter: @gbworld
> Blog: http://gregorybeamer.spaces.live.com
>
> *******************************************
> | Think outside the box! |
> *******************************************



 
Reply With Quote
 
Gregory A. Beamer
Guest
Posts: n/a
 
      07-20-2009
"sloan" <(E-Mail Removed)> wrote in
news:(E-Mail Removed):

> I should have followed up, but the "History" idea is alot cleaner than
> the SoftDelete flag/column.


It depends on needs. Some times you have hte requirement to list all itesm in
the table, deleted or not. In this case, the flag is cleaner. In general,
however, a history table is cleaner, as you end up not having to worry about
coding for a flag.

> SNIPPED




--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************
 
Reply With Quote
 
sloan
Guest
Posts: n/a
 
      07-20-2009
//Some times you have hte requirement to list all itesm in
> the table, //


Agreed.

One workaround option for this is a view that combines the data.



GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME
= 'vwEmployeeMerged')
begin
DROP VIEW dbo.vwEmployeeMerged
end
GO

CREATE VIEW dbo.vwEmployeeMerged

AS

select
EmployeeUUID, SSN
from
dbo.Employee

UNION ALL

select
EmployeeArchiveUUID as EmployeeUUID, SSN
from
dbo.EmployeeArchive


GO

print 'Put Back : vwEmployeeMerged'
GRANT SELECT ON dbo.vwEmployeeMerged TO public
GO









"Gregory A. Beamer" <(E-Mail Removed)> wrote in message
news:Xns9C4E6DBAD8954gbworld@207.46.248.16...
> "sloan" <(E-Mail Removed)> wrote in
> news:(E-Mail Removed):
>
>> I should have followed up, but the "History" idea is alot cleaner than
>> the SoftDelete flag/column.

>
> It depends on needs. Some times you have hte requirement to list all itesm
> in
> the table, deleted or not. In this case, the flag is cleaner. In general,
> however, a history table is cleaner, as you end up not having to worry
> about
> coding for a flag.
>
>> SNIPPED

>
>
>
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
>
> Twitter: @gbworld
> Blog: http://gregorybeamer.spaces.live.com
>
> *******************************************
> | Think outside the box! |
> *******************************************



 
Reply With Quote
 
Gregory A. Beamer
Guest
Posts: n/a
 
      07-20-2009
"Mark Rae [MVP]" <(E-Mail Removed)> wrote in
news:(E-Mail Removed):

> "Gregory A. Beamer" <(E-Mail Removed)> wrote in
> message news:Xns9C4E6DBAD8954gbworld@207.46.248.16...
>
>> In this case, the flag is cleaner.

>
> Don't some RDBMS provide this functionality natively...?
>
> IIRC, deleting a record in dBase / FoxPro doesn't actually delete it,
> but just marks it internally as unavailable. Isn't there a PACK
> command that you run to delete these "deleted" records permanently and
> reclaim their disk space...?


Technically, if we want to really geek out, you could conceivably get a
deleted record from the transaction log. Until you have the final commit
.... Now coding against the transaction log is a completely different
creature.

Couldn't help myself there. LOL

I would imagine there are some that will offer this feature, but most
RDBMS systems do not have the heavy handholding that something like
FoxPro (or even ... shudder ... Access) might have. And when they do
offer the free handholding, I am often leery of the system, as it
requires a pretty tight box.


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************
 
Reply With Quote
 
Gregory A. Beamer
Guest
Posts: n/a
 
      07-20-2009
"sloan" <(E-Mail Removed)> wrote in
news:#(E-Mail Removed):

> //Some times you have hte requirement to list all itesm in
>> the table, //

>
> Agreed.
>
> One workaround option for this is a view that combines the data.
>
>
>
> GO
>
> IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE
> TABLE_NAME = 'vwEmployeeMerged')
> begin
> DROP VIEW dbo.vwEmployeeMerged
> end
> GO
>
> CREATE VIEW dbo.vwEmployeeMerged
>
> AS
>
> select
> EmployeeUUID, SSN
> from
> dbo.Employee
>
> UNION ALL
>
> select
> EmployeeArchiveUUID as EmployeeUUID, SSN
> from
> dbo.EmployeeArchive
>
>
> GO
>
> print 'Put Back : vwEmployeeMerged'
> GRANT SELECT ON dbo.vwEmployeeMerged TO public
> GO
>


I would agree, but you can also get into a maintenance nightmare if this
funcationality exists across the entire enterprise. And, if you don't
know what you are doing, of course.

We have a piece of software here that creates views on top of tables for
encrypting the tables (no longer supported software, of course). It
simply renames the table to {tablename}_base and then sticks the view
with the original name. It also creates a view that does not decrypt the
fields, so it is a similar problem.

Short story, you can solve anything you might come in contact with. The
view is a decent enough solution, but can create a bit of overhead that
is unacceptable (very large apps with tons of users -- yes, most of us
are not working for Google). With SQL Server, you can get past some of
the overhead with Indexed views and the like, but that is a story for
another day.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************
 
Reply With Quote
 
Gregory A. Beamer
Guest
Posts: n/a
 
      07-21-2009
"Mark Rae [MVP]" <(E-Mail Removed)> wrote in news:uTHr$dXCKHA.4432
@TK2MSFTNGP05.phx.gbl:

> "Gregory A. Beamer" <(E-Mail Removed)> wrote in message
> news:Xns9C4E75002983Fgbworld@207.46.248.16...
>
>> as it requires a pretty tight box.

>
> Nothing wrong with that...



Is your office in your bedroom by any chance?


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************
 
Reply With Quote
 
Gregory A. Beamer
Guest
Posts: n/a
 
      07-23-2009
"Mark Rae [MVP]" <(E-Mail Removed)> wrote in news:#ovOyxlCKHA.1488
@TK2MSFTNGP03.phx.gbl:

> "Gregory A. Beamer" <(E-Mail Removed)> wrote in message
> news:Xns9C4F85EFAAC19gbworld@207.46.248.16...
>
>>>> as it requires a pretty tight box.
>>>
>>> Nothing wrong with that...

>>
>> Is your office in your bedroom by any chance?

>
> You been on my webcam again...?


ROFLMAO!


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Deleting data from the file without deleting the file first crea C++ 2 12-28-2012 11:50 PM
Database Database Database Database scott93727@gmail.com Computer Information 0 09-27-2012 02:43 AM
DataBase DataBase DataBase DataBase scott93727@gmail.com Computer Information 0 09-26-2012 09:40 AM
Deleting a File from Hardrive and Deleting a SubKey in Registry Harry Barker C++ 2 04-19-2006 09:34 AM
Deleting a record in Outlook Express address book - how? SgtMinor Computer Support 25 07-08-2005 06:18 PM



Advertisments