sql server on delete

how to get the deleted rows from result set

Page 1 of 1

4 Replies - 1854 Views - Last Post: 15 September 2009 - 07:39 AM Rate Topic: -----

#1 geethaganesh   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 16-July 09

sql server on delete

Posted 13 August 2009 - 11:32 PM

Hi,

I would like to get the deleted rows from the result set and insert into another table how to do?
Is This A Good Question/Topic? 0
  • +

Replies To: sql server on delete

#2 rgfirefly24   User is online

  • D.I.C Lover
  • member icon


Reputation: 466
  • View blog
  • Posts: 2,212
  • Joined: 07-April 08

Re: sql server on delete

Posted 14 August 2009 - 06:19 AM

you have to capture the data and put it in the other table before actually deleting it. As far as i'm aware if you've deleted a record then it is gone, no way of using it anywhere else.
Was This Post Helpful? 0
  • +
  • -

#3 ankurwase   User is offline

  • New D.I.C Head

Reputation: 4
  • View blog
  • Posts: 10
  • Joined: 12-June 09

Re: sql server on delete

Posted 15 September 2009 - 02:28 AM

View Postgeethaganesh, on 13 Aug, 2009 - 10:32 PM, said:

Hi,

I would like to get the deleted rows from the result set and insert into another table how to do?



When u delete a record its gone as far as i know ...the Best part to do that is to enter data in other table at time of deleting as rgfirefly said OR
Just Put a bit ISdeleted in the table and Changed the bit so that u dont have to make a seperate table for this..



If any query let me know..... :rolleyes:

if this post is helpful mark it as helpful!!!!!! :^:

Ankur
Was This Post Helpful? 0
  • +
  • -

#4 eclipsed4utoo   User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1536
  • View blog
  • Posts: 5,972
  • Joined: 21-March 08

Re: sql server on delete

Posted 15 September 2009 - 05:09 AM

you could also use an ON DELETE trigger. the trigger is fired before the transaction is actually committed.

http://www.sqlteam.c...triggers-part-i

triggers use two tables: inserted and deleted. when your ON DELETE trigger occurs, simply select whatever you need from the "deleted" table(its a temporary table) and insert it into the table of your choosing.

This post has been edited by eclipsed4utoo: 15 September 2009 - 05:10 AM

Was This Post Helpful? 1
  • +
  • -

#5 ForcedSterilizationsForAll   User is offline

  • D.I.C Addict

Reputation: 33
  • View blog
  • Posts: 506
  • Joined: 16-July 09

Re: sql server on delete

Posted 15 September 2009 - 07:39 AM

Triggers can be used, but they're a pain to maintain. You're adding code in yet another place and triggers can often be overlooked when debugging.

How are the records being deleted? Is there a front-end app that deletes them?

What is the volume of data that is going to be deleted? If you're working with a low volume (under 250,000 records) of data that will not have a lot of records for both deleted and non-deleted data adding a bit/tinyint field and filtering would be best.

To filter, you could use a view/function in order to avoid needing to remember to constantly filter the deleted data.

If you're working with higher volume of data, you can do the same thing as the low volume, only have a nightly process that will transfer the records that are flagged as deleted to a different table before removing them from the original table.

Using the flag method would allow you records to easily be "undeleted" if needs be. One thing you'll want to be careful about is if the record is used as a foreign key in another table.

In order to properly help and give the best solution we need more information on the set-up.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1