Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2007
    Posts
    17

    Unanswered: How to execute a trigger at very first day of month

    hello all

    actually there is a problem i want to execute a trigger at very first day of every month,so that some records of a table can be updated at very first day of every month,how it will be executed automatically at every first day of month

  2. #2
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    Hi there,

    Are you sure you want a trigger? sounds like you need a job rather than a trigger.

    Would a job with a 'run once monthly' schedule not suffice?

  3. #3
    Join Date
    Aug 2007
    Posts
    17
    hi
    no i want a trigger because it will be executed automatically at very first day of month and my database table will be updated as s.no will again start from first

    yes i want to update a s.no at very first day of month

  4. #4
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    Ok then... do something like this:



    CREATE TRIGGER aTrigger
    ON Master
    FOR UPDATE AS

    IF datepart(day,GETDATE())= 1

    BEGIN
    <insert code here>
    END

    GO

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You do not want a trigger, you want to write a script you can schedule as a job. A trigger like the above will execute every time the table is updated. A scheduled job will only run when you tell it to.
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    Quote Originally Posted by georgev
    You do not want a trigger, you want to write a script you can schedule as a job. A trigger like the above will execute every time the table is updated. A scheduled job will only run when you tell it to.
    Yep, I agree with this

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK, it makes no sense, BUT, I've got some rope handy

    Create your trigger

    Then create a job that wakes up to enable the trigger, and at the end of the day will disable the trigger
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That makes no sense.
    George
    Home | Blog

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by georgev
    That makes no sense.

    What i'd say

    BUT, it is doable
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Aug 2007
    Posts
    17
    hi sqlslammer


    name age type

    vivek 21 draft
    delhi 22 pending
    mumbai 23 approved
    delhi 24 draft
    lko 25 draft


    this is the structure of table and i want to update name vivek to mumbai
    at the very first day of month and only once on first day

    the trigger that u had given is ok but how it will execute on very first day of month at 12 a:m only ,only once not again and again whole day

  11. #11
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Quote Originally Posted by vivek_goel27
    hi sqlslammer


    name age type

    vivek 21 draft
    delhi 22 pending
    mumbai 23 approved
    delhi 24 draft
    lko 25 draft


    this is the structure of table and i want to update name vivek to mumbai
    at the very first day of month and only once on first day

    the trigger that u had given is ok but how it will execute on very first day of month at 12 a:m only ,only once not again and again whole day
    If you read the posts in the thread, you will see the answer clearly stated, including the reasons why a trigger is not advisable, and instead why an SQL Job is appropriate.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  12. #12
    Join Date
    Aug 2007
    Posts
    17
    actually i had never created a job so please help me out

  13. #13
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    From Enterprise Manager select the 'Create Job Option' and provide the details for the new job including, start time, how frequently the job will run, and what steps the job will execute. Once you've had a look at the create job screen, if you have any questions about the specifics of creating the job, I would be happy to assist further.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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