Getting deleted data after deleting

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 2005 Forums

Transact-SQL (2005)

Getting deleted data after deleting

Author

Topic

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-14 : 07:20:53

Hi everyone,
I dont know this is possible or not but this is interesting

let me explain the scenario

I have one table QDump_Patch.

From this table data is continuously being deleted
over a period of a minute.

Can I know that which data is delete before one minute
with the help of query

I have an idea to store the data somewhere and check with the existing data at the time executing query.

But i am not able to start with

Please suggest ?

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-14 : 07:59:05

you mean find data that is to be deleted before action? thats not possible. howevevr you can capture data being deleted during operation by using the DELETED table and OUTPUT clause.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-14 : 08:29:41

quote:
Originally posted by visakh16

you mean find data that is to be deleted before action? thats not possible. howevevr you can capture data being deleted during operation by using the DELETED table and OUTPUT clause.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I have done something -
but not possible me to apply the solution.
as i dont have access to create temp table on live server.

otherwise i will execute following query


IF EXISTS ( SELECT NULL FROM sys.objects WITH(NOLOCK) WHERE Object_ID = Object_ID( 'tempdb..#Temp' ) ) 
BEGIN
	DROP TABLE #Temp
END

CREATE TABLE #Temp ( RegID BIGINT, QDumpID BIGINT, SysDate DateTime ) 
INSERT INTO #Temp 
SELECT top 10 RegID, QDumpID, Sysdate FROM QDUMP_PATCH 
SELECT * FROM #Temp

And after one minute or more than one minute
i can execute the below query


SELECT D.RegID, D.QDumpID, D.Sysdate FROM #Temp T WITH(NOLOCK)
LEFT JOIN QDump_Patch D ON D.QDumpID = T.QdumpID 
WHERE D.QDumpID IS NULL

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-14 : 12:47:45

didnt understand how you're finding deleted data here

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-16 : 03:15:34

quote:
Originally posted by visakh16

didnt understand how you're finding deleted data here

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Really?

I am storing all the data in one temporary table
with the help of first query.

Then after some time like one minute or more than one minute.
as some rows will be deleted for sure.

then I will execute second query
which will give me the records
from temp table
which are no longer exists in table as those are deleted.

But this can be done if have permission to create temp table.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-16 : 04:56:41

but whats the need of all this? i didnt understand the reason for doing this at first. Why you need to get this before deletion itself? You can very well capture it during action itself right?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-16 : 05:00:19

The temporary table only exists within the scope of piece of code that created it.
Try changing from a local temp table (prefixed with one #) to a global temp table, usin double ##.

N 56°04'39.26"
E 12°55'05.63"

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-16 : 05:11:49

quote:
Originally posted by visakh16

but whats the need of all this? i didnt understand the reason for doing this at first. Why you need to get this before deletion itself? You can very well capture it during action itself right?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ok let me clear the things.

I have to monitor something in my live server database.
There is one table named 'QDump_Patch'.
From this table data is parsed and
decrypted in different format and inserted into concern tables
and deleted from Qdump_Patch Table.

But during the monitoring
i want to watch which data is parsed.
so need to get the data which are deleted from table just now.

As I have to monitor only so i cant change the business rules.
from the Stored procedures actually data is being deleted.
I am not doing any developement or modification.
I jst need to monitor.

Hope you got it.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-16 : 05:13:26

quote:
Originally posted by Peso

The temporary table only exists within the scope of piece of code that created it.
Try changing from a local temp table (prefixed with one #) to a global temp table, usin double ##.

N 56°04'39.26"
E 12°55'05.63"

Correct i will do it.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Sachin.Nand

2937 Posts

Posted - 2010-08-16 : 07:18:01

quote:
Originally posted by vaibhavktiwari83

quote:
Originally posted by Peso

The temporary table only exists within the scope of piece of code that created it.
Try changing from a local temp table (prefixed with one #) to a global temp table, usin double ##.

N 56°04'39.26"
E 12°55'05.63"

Correct i will do it.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

But beware the global temporary tables are common for all users.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-16 : 10:55:30

As Idera said think if in case your code will be executed simultaneously by more than 1 user

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources