Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2011
    Posts
    8

    Unanswered: Reset table field value to 1

    Hi there

    I need an automated process where i can reset the field value to 1 on a table at the begining of each year (first day of the new year).How would you go around this? Triggers or stored procedure? please help with the code as well for example.

    Can you have more than 1 After insert,update triggers on one table and how does it affect the performance and everything around the table.

    Thanks in advance

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    You could use a trigger . . . and every single time someone touched this table, the trigger would test if the date is the first of the year, and it would run an update query against that field/table. You would also have to record, somewhere, that this trigger had fired for the year, because you don't want it firing multiple times on the first day of the year. And what if the first day of the year is not a business day and no one goes into that table on the first day of the year. You'd need to account for that. And since this trigger is firing on every contact with the table, think of the excess load that you would be adding to your system. Talk about a bad design . . .

    In case you haven't gotten my point, triggers are BAD NEWS. BAD NEWS. BAD NEWS. Don't use them.

    Instead, you should create a scheduled job that would cause the update.

    You don't need a trigger, you don't need a stored procedure, you just need to have the schedule job run the update at 12:00:00am on January 1 of each year.

    Nothing could be simpler.
    Last edited by PracticalProgram; 12-29-11 at 12:14.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by Sally1053 View Post
    ... where i can reset the field value to 1 on a table at the begining of each year (first day of the new year)
    Where is the last used sequence number or the new value of the number stored?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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