none
Delete automatically record after 60 seconds after inserted into the table RRS feed

  • Question

  • Hi,

       I have a scenario to delete a record after insert into the particular table only after 60 seconds. i mean, if one record got inserted into the table1, then after 60 seconds, that particular record should got deleted from the same table table1.

     

      I have tried with Trigger, with Delay command, but the application itself waits or stuck for 60 seconds to trigger to get completed.. Is there anyway to accomplish this..

     

    Thanks in advance.

     

    Regards,
    Fazlu. 

    Monday, November 21, 2011 11:25 AM

Answers

  •  

    from 2005+..

    VT


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker


    Monday, November 21, 2011 1:20 PM
  • Here is a power shell script . You can schedule this is windows task scheduler

     

    	[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
    	[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
    	[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
    	[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null
    	$server=new-object ("Microsoft.SqlServer.Management.Smo.Server") "yourservername"
    	$db=$server.Databases["yourDB"]
    	$db.ExecuteNonQuery("delete from youtable where DATEADD(S,60,recCreated)<GETDATE()")
    

     

    VT


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    Monday, November 21, 2011 2:05 PM

All replies

  • Does the table has datetime column to indicate when the record was added.. if so 

    create a sql agent job that runs every 10 sec with following delete script

    delete from youtable where DATEADD(S,60,recCreated)<GETDATE()

     

    VT


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    • Edited by SimpleSQL Monday, November 21, 2011 11:47 AM
    Monday, November 21, 2011 11:33 AM
  • Hi,

       Valid Point. Yes my table has Datetime Column and it has the current time when the record got inserted. But, SQL Agent Job, we can schedule it in minute or hours only, i dont see we can be able to schedule it in seconds anyway.. any idea..

     

    Thanks for the Reply,


    Regards,

    Fazlu

    Monday, November 21, 2011 1:13 PM
  •  

    from 2005+..

    VT


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker


    Monday, November 21, 2011 1:20 PM
  • Is this SQL Server 2008....?  I am using SQL Server 2005

     

    Regards,

    Fazlu

    Monday, November 21, 2011 1:32 PM
  • Yes i am using SQL 2008 r2

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    Monday, November 21, 2011 1:44 PM
  • Here is a power shell script . You can schedule this is windows task scheduler

     

    	[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
    	[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
    	[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
    	[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null
    	$server=new-object ("Microsoft.SqlServer.Management.Smo.Server") "yourservername"
    	$db=$server.Databases["yourDB"]
    	$db.ExecuteNonQuery("delete from youtable where DATEADD(S,60,recCreated)<GETDATE()")
    

     

    VT


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    Monday, November 21, 2011 2:05 PM