Implementing a Deleted Items Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Implementing a Deleted Items Table

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2002-07-09 : 11:21:24

I'd like to implement a "deleted rows" table. I can brute force it by listing every field, but was wondering if there were a more elegant syntax.

Once the selection of records to be deleted is known;

select * from users where ....

Is there a sql insert into deletedusers that will copy the rows?

insert into oldusers ....

using the select, copying all fields from all rows?

SamC

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-07-09 : 11:56:47

Another solution is a BIT or INT field on the user table that marks the record as deleted. That way, all of your referential integrity stays intact. It's also much easier to impliment.
The downsides are that all of your select stored procs must be changed to get only the "non deleted records". The Other disadvantage is that if you delete lots of rows from this table all the time, your table will get large and stay large, as will indexes on that table, thus slowing things down a bit.

Michael

To answer your actual question, I think you need to use something like this. This assumes OldUsers exists and is exactly like Users

INSERT INTO OldUsers
SELECT * from Users where ToBeDeleted=1

You might need an INSTEAD OF Triggger (sql2000) to handle this. Look it up in the BOL.

<Yoda>Use the Search page you must. Find the answer you will.

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-07-09 : 11:57:50

Not sure what the exact issue is but you can insert into one table
from another (assuming they have the same structure) with...


insert DeletedUsers
select * from ActiveUsers
where Deleted = 'True'

Justin

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-07-09 : 11:59:01

Damn, looks like Micheal is quicker on the draw than me

Justin

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-07-09 : 12:15:33

Justin, it's that snazzy new title :)

Michael

<Yoda>Use the Search page you must. Find the answer you will.

SamC
White Water Yakist

3467 Posts

Posted - 2002-07-09 : 12:28:24

Hmmm.. I thought I had tried this previously...

Tried it again, got an error I don't recall so maybe I hadn't tried it before.

INSERT INTO OLDUSERS
SELECT * FROM USERS WHERE ....

Gives up:
An explicit value for the identity column in table oldusers can only be specified when a column list is used and IDENTITY_INSERT is on.

It would be great not to enumerate the desired columns in the select. Any way around this problem without enumerating the fields?

BTW - I have thought of setting a "DeleteBit=1" but didn't want to modify all the routines that work on the table. Acknowledging that I am "VIEW" challenged, - here comes a novice question - could the routines point to a VIEW of the user table which held only non-deleted items and still update/report without change?

SamC

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-07-09 : 13:15:44

quote:

Gives up:
An explicit value for the identity column in table oldusers can only be specified when a column list is used and IDENTITY_INSERT is on.

Your 'deleted' tables should not have the identity column - the identity value will come from the 'base' table rather than being auto-generated.

quote:

BTW - I have thought of setting a "DeleteBit=1" but didn't want to modify all the routines that work on the table. Acknowledging that I am "VIEW" challenged, - here comes a novice question - could the routines point to a VIEW of the user table which held only non-deleted items and still update/report without change?

You can, and in fact that's likely the best solution. If you're running SQL Server 2K, which I recommend, you can define materialized views for acceptable performance.

Jonathan Boott, MCDBA

SamC
White Water Yakist

3467 Posts

Posted - 2002-07-09 : 13:40:10

Constraint Violating Yak Guru,

I see the steps as follows:

1 - Rename table "USERS" to "USERS123"
2 =
Create VIEW USERS AS
SELECT * FROM USERS WHERE DeletedUser=0

-------------
Seems to work. This solution was so easy it feels like I got something for nothing.
-------------

Now if I can just get the special characters out of those strings... but that's another post.

Thank you for your feedback.

Sam

azim
Starting Member

29 Posts

Posted - 2002-07-09 : 14:27:43

Dear SamC
I Think You Want A Trigger To Solve Your Problem

 
Creat Trigger Update_Deleted_User On Table_User
For Delete As
Begin
Insert Into Deleted_User(Select * From Table_User Where 
Table_User.UserId =
(Select UserId From Deleted Where DEleted.UserId=Table_User.UserId))
End

azim

Edited by - Azim on 07/09/2002 14:49:57

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources