Deleting a record from a database

[bthumber]

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?

 

[sloan]

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

 

[Gregory A. Beamer]

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).

 

[sloan]

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]

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.

 

[sloan]

//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]

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]

//Some times you have hte requirement to list all itesm in

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]

Nothing wrong with that... ;-)

Is your office in your bedroom by any chance? ;-)

 

[Gregory A. Beamer]

You been on my webcam again...?

ROFLMAO!