Post reply

Trigger to delete old data before Insert

  • April 23, 2013 at 9:42 am

    #394655

    Hi guys ..

    i am trying to create Trigger on sql 2000 .

    creating trigger which deletes old data more then 30 days and then do inserts.

    any scripts for this .

    would be great help

    Thanks

  • April 23, 2013 at 11:23 am

    #1609196

    HI

    It would be much more simple if you write a stored procedure, or create a DTS package that is executed from a SQL job on a daily basis. Then perform inserts all day long until the next time the SQL job executes.

    If not, you would have to write code in your sproc to find that row(s) before the insert, delete the row(s) and then insert. Same thing that a modern day MERGE statement does.

    Andrew SQLDBA

  • April 23, 2013 at 6:57 pm

    #1609319

    INSTEAD OF trigger would be useful for the task.

    Look up BOL for details.

    But I support Andrew in his suggestions - regular job is more preferrable for such tasks.

    Or call deletion before insertion from a procedure.

    Unless you've got some special requirements.

    And make sure the datetime column you use for defining "old data" is the 1st column in the clustered index.

    !!!

    it's not a suggestion, it's a requirement for such tasks.

    _____________
    Code for TallyGenerator

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply