 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / March 2008Removing something from a table at a specified time
Hi everyone, I hope that I'm in the right place! Apologies if I'm not. Hope theat you can help me with this one as I'm stuck. I have a table in a SQL Server 2005 database that contains 3 columns. The first is a key, the second the timestamp when the row was written and the third is the expiry time in mimutes. For example: 12342 20/03/08 11:12:23.323 30 This is my problem - I want some sort of process (sproc, utf perhaps) that removes the row and any dependencies this row has in other tables from the database when the expirt time has passed - which in this case would be: 20/03/08 11:42:23.323 (like a cascade deletion policy) Anyway I would appreciate any comments/suggestions/ideas/code samples that you may be able to offer me by way of help. Thank you, Al. PS: Please note that these columns are not fixed in stone. For example it would be quite easy to add a fourth like the the sum of the 2 time fields which would then become: 20/03/08 11:42:23.323 Hi Can you please post DDL + sample data + an expected result? For example CREATE TABLE #test (col1 INT, col2 DATETIME,col3 VARCHAR(20)) BTW , why do you store three columns instead of two, col2 in the above case will conntain minutes portion as well Anyway INSERT INTO #test VALUES (.........) > Hi everyone, > [quoted text clipped - 20 lines] > it would be quite easy to add a fourth like the the sum of the 2 time > fields which would then become: 20/03/08 11:42:23.323 > Hi > Can you please post DDL + sample data + an expected result? [quoted text clipped - 37 lines] > > - Show quoted text - Hi Uri, All that I have so far is: USE myDatabase CREATE TABLE myTable ( Identifier INT IDENTITY NOT NULL, InsertTime DATETIME NOT NULL, Duration INT NOT NULL ) Example of data would be like the above: 12342 20/03/08 11:12:23.323 30 with the constraint that when the system time value exceed "InsertTime + Duration" - the row and all child depends are removed. Does this help any? Hi You can create a trigger that does the job (UNTESTED) CREATE TRIGGER my_trig ON Mytable FOR INSERT AS DELETE FROM myTable WHERE EXISTS (SELECT * FROM inserted I WHERE I.Identifier=myTable.Identifier AND myTable.InsertTime< I.InsertTime AND myTable.Duration< I.Duration) Or if you do that throu a stored procedure DECLARE @dt DATETIME, @Duration INT SET @dt =GETDATE() SET @Duration=20 DELETE FROM myTable WHERE InsertTime<@dt AND Duration<@Duration SELECT * FROM myTable > Hi > Can you please post DDL + sample data + an expected result? [quoted text clipped - 39 lines] > > - Show quoted text - Hi Uri, All that I have so far is: USE myDatabase CREATE TABLE myTable ( Identifier INT IDENTITY NOT NULL, InsertTime DATETIME NOT NULL, Duration INT NOT NULL ) Example of data would be like the above: 12342 20/03/08 11:12:23.323 30 with the constraint that when the system time value exceed "InsertTime + Duration" - the row and all child depends are removed. Does this help any? The only problem with that Uri is that in the case that a time is inserted into the table say 123456 20/03/2008 09:00:00.000 5 it would expire at 9:05 am on March 20th. Using the trigger option could lead to nothing being inserted for a random amount of time, and in this case possibly longer than 5 minutes. Thus the row would be left in there for an undeterminable amount of time, thus whatever the time limit is for would be null and void logically but as far as the application is concerned it's still there and valid. It would be nice to use the triggers, but I think pulling the data from a view like suggested above would work much better. I would take that suggesstion by Rick except depending on how many rows are inserted per day, possibly running the sproc every x hours or so. It all depends on the workload in that table. -Sean > Hi > You can create a trigger that does the job (UNTESTED) [quoted text clipped - 19 lines] > > SELECT * FROM myTable Sean I see what you mean. That we need addition check within a trigger and not to simple DELETE. What to check? But perhaps the OP wants those rows left in the table ? The only problem with that Uri is that in the case that a time is inserted into the table say 123456 20/03/2008 09:00:00.000 5 it would expire at 9:05 am on March 20th. Using the trigger option could lead to nothing being inserted for a random amount of time, and in this case possibly longer than 5 minutes. Thus the row would be left in there for an undeterminable amount of time, thus whatever the time limit is for would be null and void logically but as far as the application is concerned it's still there and valid. It would be nice to use the triggers, but I think pulling the data from a view like suggested above would work much better. I would take that suggesstion by Rick except depending on how many rows are inserted per day, possibly running the sproc every x hours or so. It all depends on the workload in that table. -Sean > Hi > You can create a trigger that does the job (UNTESTED) [quoted text clipped - 19 lines] > > SELECT * FROM myTable > Sean > I see what you mean. That we need addition check within a trigger and not [quoted text clipped - 46 lines] > > - Show quoted text - Gentlemen, I appreciate your posts. I'm leaning toward a View instead of a table but won't I have to use a trigger on the view as well? Or is there something I'm missing? Slightly confused, Al. No - you wouldn't use a trigger at all. Create teh view off of the underlying tables as suggested by Rick. Once that's done, create a stored procedure also as Rick suggests that deletes the values out of the underlying tables and then schedule that stored procedure to run every so often, daily, hourly, whatever you think is necessary. the view will take care of which ones should and shouldn't show up and the stored procedue will run cleanup whenever it's fired. On Mar 20, 12:49 pm, "almu...@altavista.com" <almu...@altavista.com> wrote: > > Sean > > I see what you mean. That we need addition check within a trigger and not [quoted text clipped - 60 lines] > > - Show quoted text - > Hi everyone, > [quoted text clipped - 20 lines] > it would be quite easy to add a fourth like the the sum of the 2 time > fields which would then become: 20/03/08 11:42:23.323 Based on your scenario, you would need something that essentially runs continuously. Is there actually a reason for that? You could do it with a SQL Job that runs every minute or two. Without knowing your actual scenario and the reasons for removing these rows right away, I would probably create a sproc that deleted all the rows and related rows in the child tables. I would then call that sproc from the scheduled job. In addition to that, I would also only run that job once a day. I would modify the current queries against that table and have them ignore rows where the expiry had passed already. If that is too difficult, then I would probably rename the table and create a view on the newly renamed table (using the old table name) that only returned rows where GETDATE() <= (timestamp + expiry). Just my .02 Rick Sawtell |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.