Removing 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:
1234220/03/08 11:12:23.32330
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 (.......)
<almurph@.altavista.com> wrote in message
news:b7683713-7aa0-4a1c-80ae-ede8fe9f26e7@.s12g2000prg.googlegroups.com...
> 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
|||<almurph@.altavista.com> wrote in message
news:b7683713-7aa0-4a1c-80ae-ede8fe9f26e7@.s12g2000prg.googlegroups.com...
> 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
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
|||On Mar 20, 12:03Xpm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> 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 XVALUES (.......)
> <almu...@.altavista.com> wrote in message
> news:b7683713-7aa0-4a1c-80ae-ede8fe9f26e7@.s12g2000prg.googlegroups.com...
>
>
>
>
>
> - Show quoted text -
Hi Uri,
All that I have so far is:
USE myDatabase
CREATE TABLE myTable
(
IdentifierINTIDENTITYNOT NULL,
InsertTimeDATETIMENOT NULL,
DurationINTNOT 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
<almurph@.altavista.com> wrote in message
news:a53fb8ab-acb3-4daf-9be0-4a012d242f56@.i7g2000prf.googlegroups.com...
On Mar 20, 12:03 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> 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 (.......)
> <almu...@.altavista.com> wrote in message
> news:b7683713-7aa0-4a1c-80ae-ede8fe9f26e7@.s12g2000prg.googlegroups.com...
>
>
>
>
>
> - 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
On Mar 20, 8:38Xam, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> You can create a trigger that Xdoes the job X (UNTESTED)
> CREATE TRIGGER my_trig ON Mytable FOR INSERT
> AS
> DELETE FROM myTable XWHERE EXISTS (SELECT * FROM inserted I WHERE
> I.Identifier=myTable.Identifier
> AND myTable.InsertTime< I.InsertTime AND myTable.Duration< I.Duration)
> Or Xif 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
|||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 ?
"Sean" <ColdFusion244@.gmail.com> wrote in message
news:6660ec37-baeb-498a-944f-93c01326ad14@.p25g2000hsf.googlegroups.com...
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
On Mar 20, 8:38 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> 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
|||On Mar 20, 2:01Xpm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Sean
> I see what you mean. That we need addition check within a trigger Xand Xnot
> to simple DELETE. What to check?
> But perhaps the OP wants those rows left in the table ?
> "Sean" <ColdFusion...@.gmail.com> wrote in message
> news:6660ec37-baeb-498a-944f-93c01326ad14@.p25g2000hsf.googlegroups.com...
> The only problem with that Uri is that in the case that a time is
> inserted into the table say
> 123456 X X 20/03/2008 09:00:00.000 X X 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
> On Mar 20, 8:38 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
>
>
>
>
>
>
>
> - 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:49Xpm, "almu...@.altavista.com" <almu...@.altavista.com>
wrote:
> On Mar 20, 2:01Xpm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
> Gentlemen,
> X X 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.- Hide quoted text -
> - Show quoted text -