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

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.

Where is the last used sequence number or the new value of the number stored?