none
Trigger without UPdate,INSERT and DELETE

    Question

  • I have SQL SERVER 2005 and I have a table with a Cloumn DATETIME data Type,

    I want to ceate a trigger that checks the table rows and delete the record (s) when it has (DATETIME colume value - today date()) >20  days.

    Is it possible to write a trigger for that? 

    I am new to SQL Server and trigger.

    Thanks,

    MK 

     

    Monday, September 20, 2010 10:06 PM

Answers

  • hi,

    BTW, I do apologize for the wrong code provided.. it would be better to add the maximal retaining time to the MIN(column) value and check it against "now", similarly to

    DECLARE @nextRun datetime;
    SELECT @nextRun = DATEADD(DAY, 20, MIN(dt))
     FROM dbo.t;
    SELECT @nextRun;
    IF @nextRun < DATEADD(MINUTE, 1, GETDATE())
     SET @nextRun = DATEADD(MINUTE, 1, GETDATE());
    SELECT @nextRun;

    I beg your pardon.. regards


    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/ - http://www.hotelsolericcione.de
    Tuesday, September 21, 2010 2:28 PM
    Moderator

All replies

  • hi,

    triggers, as the name implies, are triggered by a DML operation, that's to say insert, update or delete statement.. what you need, on the other side, does not seems to me to be dependent on such a scenario.. and there's no actual way to do it as it would involve the engine to subscribe to an event (column - GETDATE() > 20 days) which is not supposed to be fired by the engine it self.. so the "perfect" solution is not available... a viable solution would perhaps be to perform a scheduled action to delete all the qualifying rows from the table, and you can even schedule it to be performed more than once a day accordingly to (actually only partially) your needs as it would be to much expensive to schedule it once a minute or the like.. so you can have a partial solution you should be satisfied with..

    as regard the "how" part, SQLExpress does not feature the SQL Server Agent, the native scheduler of SQL Server, where you would define a job to be performed as required (again, not that strictly as you imply in your indications, but I'd say "sufficient" for the task) with a Transact-SQL job step where you'd code the DELETE statement.. so you are required to depend on alternate schedulers, like the SCHTASKS/WIN AT native schedulers.. write a cmd file where you execute a statement against the SqlCMD.exe command line SQL Server tool (http://msdn.microsoft.com/en-us/library/ms180944.aspx).. once coded, define an OS based scheduled task via the Scheduled Tasks user interface... alternatively, you can perhaps rely on another feature available in SQL Server and SQLExpress, the Service Broker, an architecture and feature not realy very used in SQLExpress world becouse of it's SKU limitations, but Roger Wolter published an excellent "accademical" article on how to schedule a database backup with it, so you can perhaps modify the statement to be a DELETE command and not a BACKUP one as indicated in http://blogs.msdn.com/b/rogerwolterblog/archive/2006/04/13/575974.aspx.. perhaps, in this scenario, you can even query the table for "next" run time similarly to

    DECLARE @nextRun datetime;
    SELECT @nextRun = MIN(dt)
    FROM dbo.t
    WHERE DATEDIFF(DAY, dt, GETDATE()) > 20;
    IF @nextRun < DATEADD(MINUTE, 1, GETDATE())
    SET @nextRun = DATEADD(MINUTE, 1, GETDATE());
    SELECT @nextRun;

    and use that @nextRun to reset the conversation timer of the unit.. obviously @nextRun must be greater than NOW or it will not be fired anymore :)

    regards


    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/ - http://www.hotelsolericcione.de
    Monday, September 20, 2010 11:50 PM
    Moderator
  • hi,

    BTW, I do apologize for the wrong code provided.. it would be better to add the maximal retaining time to the MIN(column) value and check it against "now", similarly to

    DECLARE @nextRun datetime;
    SELECT @nextRun = DATEADD(DAY, 20, MIN(dt))
     FROM dbo.t;
    SELECT @nextRun;
    IF @nextRun < DATEADD(MINUTE, 1, GETDATE())
     SET @nextRun = DATEADD(MINUTE, 1, GETDATE());
    SELECT @nextRun;

    I beg your pardon.. regards


    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/ - http://www.hotelsolericcione.de
    Tuesday, September 21, 2010 2:28 PM
    Moderator
  • Thanks Andrea.

    I think it would be better or simpler for me to use Quartz.net.

    Regards,

    MK 

    Tuesday, September 21, 2010 8:41 PM