| Author |
Topic |
|
vibhav
Starting Member
28 Posts |
 Posted - 2008-03-26 : 11:37:44
|
| Can anybody help me in writing a SQl trigger to delete a record when inserted after 70 seconds.? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-03-26 : 12:44:00
|
| After 70 second from what? didnt get your question completely. |
 |
|
|
vibhav
Starting Member
28 Posts |
Posted - 2008-03-26 : 13:34:26
|
quote:
Originally posted by visakh16
After 70 second from what? didnt get your question completely.
Let'say, I insert a record into a table at a x time.After x+70seconds the trigger should delete the record inserted into the table. I wish I am clear this time. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-03-26 : 13:38:44
|
| Why such a requirement? Why do you want to do this? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-03-26 : 14:01:41
|
| You don't want to do it in the trigger presumably as the table will be locked for the period.You can have a default getdate() column on the table and a scheduled task which deletes rows from the table after 70 secs. The trigger could add the row id/date to a table for the scheduled task to act on if you don't want to change the structure or for efficiency.The scheduled task could run evey second or have a loop with delay in it to run continuously.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
vibhav
Starting Member
28 Posts |
Posted - 2008-03-26 : 14:02:35
|
quote:
Originally posted by visakh16
Why such a requirement? Why do you want to do this?
We have an application which reads this table and runs necessary process .It is a kind of sync process(60seconds) with my custom application. So when my application reads the records it should not process the same record twice. So as per design, we want to read it only once. So we have decided to delete any record after being read in <60seconds. Such that this inserted record will not be available anymore.Do you see something better? |
|
|
|
vibhav
Starting Member
28 Posts |
Posted - 2008-03-26 : 14:06:05
|
quote:
Originally posted by nr
You don't want to do it in the trigger presumably as the table will be locked for the period.You can have a default getdate() column on the table and a scheduled task which deletes rows from the table after 70 secs. The trigger could add the row id/date to a table for the scheduled task to act on if you don't want to change the structure or for efficiency.The scheduled task could run evey second or have a loop with delay in it to run continuously.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
Here each record has to be deleted in 70 seconds and I have full control on when to read the records(Will increase the sync time). This record has to go by the time I start my next read process of the table. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-03-26 : 14:06:50
|
| I think a better approach will be to keep a bit field in table which indicates whether record has been read and write procedure which deletes all records in table with bit set in every 60 seconds. You may schedule a job to execute the SP in every 1 min |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-03-26 : 14:07:52
|
| and this bit will set by the application while it reads the records. |
|
|
|
NeilG
Aged Yak Warrior
530 Posts |
Posted - 2008-03-26 : 14:10:28
|
| Try something like thiscreate trigger [TriggerName]on [TableName]aswaitfor delay '00:00:70'[rest of you delete trigger code]but I really can not see what the point of the delay is as this will cause some real performance issues on a OLTP system |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-03-26 : 14:12:20
|
quote:
Originally posted by vibhav
quote:
Originally posted by nr
You don't want to do it in the trigger presumably as the table will be locked for the period.You can have a default getdate() column on the table and a scheduled task which deletes rows from the table after 70 secs. The trigger could add the row id/date to a table for the scheduled task to act on if you don't want to change the structure or for efficiency.The scheduled task could run evey second or have a loop with delay in it to run continuously.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
Here each record has to be deleted in 70 seconds and I have full control on when to read the records(Will increase the sync time). This record has to go by the time I start my next read process of the table.
Not sure how your comment affects the solution - maybe I'm not understanding something. Is there a reason why my suggestion wouldn't work?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-03-26 : 14:13:42
|
quote:
Originally posted by NeilG
Try something like thiscreate trigger [TriggerName]on [TableName]aswaitfor delay '00:00:70'[rest of you delete trigger code]but I really can not see what the point of the delay is as this will cause some real performance issues on a OLTP system
Have a look at my first post as to why this isn't a good idea (the row would never be committed) and how to get round it.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
vibhav
Starting Member
28 Posts |
Posted - 2008-03-26 : 14:32:54
|
quote:
Originally posted by visakh16
I think a better approach will be to keep a bit field in table which indicates whether record has been read and write procedure which deletes all records in table with bit set in every 60 seconds. You may schedule a job to execute the SP in every 1 min
Here my application can only read but will be painstaking to modify the table by setting the bit. How about running a procedure to delete the record every 70 seconds? |
|
|
|
vibhav
Starting Member
28 Posts |
Posted - 2008-03-26 : 14:34:19
|
quote:
Originally posted by nr
You don't want to do it in the trigger presumably as the table will be locked for the period.You can have a default getdate() column on the table and a scheduled task which deletes rows from the table after 70 secs. The trigger could add the row id/date to a table for the scheduled task to act on if you don't want to change the structure or for efficiency.The scheduled task could run evey second or have a loop with delay in it to run continuously.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
Seems to be also a viable solution. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-03-26 : 14:42:29
|
quote:
Originally posted by vibhav
quote:
Originally posted by visakh16
I think a better approach will be to keep a bit field in table which indicates whether record has been read and write procedure which deletes all records in table with bit set in every 60 seconds. You may schedule a job to execute the SP in every 1 min
Here my application can only read but will be painstaking to modify the table by setting the bit. How about running a procedure to delete the record every 70 seconds?
Confused - that was what I was suggesting.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
vibhav
Starting Member
28 Posts |
Posted - 2008-03-26 : 15:32:46
|
quote:
Originally posted by nr
quote:
Originally posted by vibhav
quote:
Originally posted by visakh16
I think a better approach will be to keep a bit field in table which indicates whether record has been read and write procedure which deletes all records in table with bit set in every 60 seconds. You may schedule a job to execute the SP in every 1 min
Here my application can only read but will be painstaking to modify the table by setting the bit. How about running a procedure to delete the record every 70 seconds?
Confused - that was what I was suggesting.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
Since anyway we are going to delete then I think there is no need to have a bit and check if it read or not. Can anybody give any procedure to run a scheduled task on the database to delete the record?Thanks |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-03-26 : 15:39:11
|
| I reading over this and my assumption is that the data in this table that you want to act upon doens't really matter. What if the process thjat extracts the records to process doesn't run for 30 minutes? Should those records that have been sitting there get deleted since they are over 70 seconds old?Feel free to use one of the suggestions above and just schedule a job to delete the data every 60,70 or N seconds. But, if the data is important and you actually need to guaranteethat you proccess each row, then you'll want to come up with a real solution. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-03-26 : 15:43:35
|
| Look at the agent - that's where you create a scheduled task.If you don't want to add a date to the table and don't already have one then you will need another table as my previous post - this would be better anyway.The scheduled sp would be something likecreate proc DelRowsdeclare @tbl table (id int)insert @id select id from deltbl where dte < dateadd(ss,-70,getdate())delete tblwhere id in (select id from #a)delete deltblwhere id in (select id from #a)goreplace id by whatever your pk on the table is.If PK's can get reused then you will need a transaction around that or error processing.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-03-26 : 15:49:56
|
| I'm assuming that you have many rows in the table inserted at different times and you ant each one deleted after 70 secs.You could also consider a view to access the table so that only those rows less than 70 secs old are available - that would mean you don't have to physically dellete and could have a clearup task run overnight. Might help if you find you get blocking.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
vibhav
Starting Member
28 Posts |
Posted - 2008-03-26 : 16:43:01
|
quote:
Originally posted by Lamprey
I reading over this and my assumption is that the data in this table that you want to act upon doens't really matter. What if the process thjat extracts the records to process doesn't run for 30 minutes? Should those records that have been sitting there get deleted since they are over 70 seconds old?Feel free to use one of the suggestions above and just schedule a job to delete the data every 60,70 or N seconds. But, if the data is important and you actually need to guaranteethat you proccess each row, then you'll want to come up with a real solution.
Yes you are also right. My application will definitely read the records.No doubts about it.How about moving this record into another table? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-03-26 : 16:53:23
|
| Just change the sp I gave earlier to do an insert into another table before the delete.Maybe you should give some more information about what you are trying to achieve.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Next Page
|
trigger on Insert to delete record