Results 1 to 2 of 2

Thread: I need Help

  1. #1
    Join Date
    Dec 2001
    Location
    India
    Posts
    4

    Unanswered: I need Help

    Hi Freinds,
    I need some help on SQL Server.
    I need to create a trigger on the update of a particular field in a table.
    This trigger has to perform some action after an hour since this field is updated.
    Java has a sleep function, does SQL also have something like that.

    Please (if ull can)Reply on my personal id.
    parul.gulati@india.birlasoft.com

    Thanks and Regards,
    Parul

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    SQL Server has:

    WAITFOR { DELAY 'time' | TIME 'time' }

    The WAITFOR statement suspends the execution of a connection until either:

    i) specified time interval has passed.
    ii) specified time of day is reached.

    Example:
    WAITFOR DELAY '00:00:02'

    However I would not use this within a trigger. Since a trigger is fired on a event (INSERT,UPDATE,DELETE), that event will also be suspend for an hour (your example), untill the trigger has completed.

    If you need to perform some sort of action after an hour, look at:

    1) use xp_cmdshell to call an external program which will sleep for the hour, then connect to the DB and perform the action.

    2) Look at sp_OA procedures (sp_OACreate, sp_OAMethod, ...) which could be used to run a COM object program.

    3) Have the trigger load a seperate table or set a flag indicating an action to be taken, then have a seperate stored procedure perform your work for you, the procedure could be scheduled using SQL Server's scheduler to run every 15 minutes or so.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •