retrieving delete info from a log 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

Transact-SQL (2000)

retrieving delete info from a log table

Author

Topic

yossibaram
Yak Posting Veteran

82 Posts

Posted - 2002-08-26 : 08:37:28

Hi,
I have a log table that includes Start-Time field(specify the time record was inserted to the table)and End-Time field(specify the time the record was updated or deleted, 9/9/9999 as default).
The table is filled from a trigger.
If new Insert:
Product=11 Start_Time=11:00, End_Time=9/9/999(default)
If Update, the old record End_Time is updated
Product=11 Start_Time=11:00, End_Time=12:00
And we have the updated record
Product=11 Start_Time=12:00(the same time as above), End_Time=9/9/999(default)
If we delete:
Product=11 Start_Time=12:00, End_Time=13:00

I have a problem writing a SP that returns all deleted products.
When a product deleted Start-Time and End-Time have values and no record is added to the log table), the difference with update is that we have new record(the modifyed)with new Start-Time value.
How the query should look like if i want to retrieve only deleted products between 2 time intervals.
I attach a SP tha returns only products between time interval but without the additional checking require.

/****** Object: Stored Procedure dbo.GetDeleted_From_Log Script Date: 8/25/20002 - Retrievs records that were deleted from the db between time interval ******/
CREATE PROCEDURE GetDeleted_From_Log
@StartTime dateTime
,@EndTime dateTime
AS
SELECT
Product_Num
FROM
ocdb_History_Product
WHERE
ocdb_History_EndDateTime
BETWEEN
@StartTime
AND
@EndTime

Hope you understand the mess…
Thanks
Yossi

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-08-26 : 08:52:17

CREATE PROCEDURE GetDeleted_From_Log
@StartTime dateTime,
@EndTime dateTime
AS
SELECT h.Product_Num
FROM ocdb_History_Product AS h
WHERE h.ocdb_History_EndDateTime BETWEEN @StartTime AND @EndTime
AND NOT EXISTS(SELECT * FROM Products AS p WHERE p.Product_Num = h.Product_Num)

... give that a try ... assuming this history table only stores the history of a single table ...

yossibaram
Yak Posting Veteran

82 Posts

Posted - 2002-08-26 : 09:10:24

Thanks for the quick reply.
Yes, the history table only stores the history of a single table but what happens if after delet I insert the same product again?
If it will be between time interval I won't get the record that was deleted.
I need to catch those records that have end_time value but don't have the exact time value in the start_time for the same product.
Please advice
Thanks
Yossi

quote:

CREATE PROCEDURE GetDeleted_From_Log
@StartTime dateTime,
@EndTime dateTime
AS
SELECT h.Product_Num
FROM ocdb_History_Product AS h
WHERE h.ocdb_History_EndDateTime BETWEEN @StartTime AND @EndTime
AND NOT EXISTS(SELECT * FROM Products AS p WHERE p.Product_Num = h.Product_Num)

... give that a try ... assuming this history table only stores the history of a single table ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources