Update SQL table using trigger

Question

i'm having table with columns

[ID] [bigint] IDENTITY(1,1) NOT NULL,
[CompanyID] [bigint] NULL,
[EmployeeName] [nvarchar](50) NULL,
[EmployeeGSM] [nvarchar](50) NULL,
[EmployeeNumberOfDaysOfAnnualLeaveInEachMonth] [decimal](5, 2) NULL,
[EmployeeTotalNumberOfAnnualLeave] [decimal](7, 2) NOT NULL

on every month 1st at 00:00:00 i need to update the column EmployeeTotalNumberOfAnnualLeave increment by EmployeeNumberOfDaysOfAnnualLeaveInEachMonth value using trigger

With Regards

You may be looking for a scheduled job as opposed to a trigger — Tom
– Tom, Commented Jan 22, 2014 at 13:47
Triggers are meant to take action when some other action is performed in a table, not for executing scheduled tasks. — Justin Niessner
– Justin Niessner, Commented Jan 22, 2014 at 13:47
thank you...if i create a job and scheduled it....if backup my database and restored in other pc do i need to create a new job? — Mohammed
– Mohammed, Commented Jan 22, 2014 at 13:53
It would almost always be better to store the raw information about e.g. leave taken as discrete rows in an appropriate table and then just calculate these totals when required. When you store totals that could be computed from other data, you're immediately opening yourself up to the possibility that the total is incorrectly updated. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jan 22, 2014 at 14:23

Tom · Accepted Answer · 2014-01-22 13:58:28Z

1

Assuming the SQL should be UPDATE Table SET EmployeeTotalNumberOfAnnualLeave = EmployeeTotalNumberOfAnnualLeave + EmployeeNumberOfDaysOfAnnualLeaveInEachMonth

You could:

1) Create a stored procedure to perform the task:

CREATE PROCEDURE UpdateEmployeeTotalNumberOfAnnualLeave
AS
BEGIN
    UPDATE Table 
    SET EmployeeTotalNumberOfAnnualLeave = EmployeeTotalNumberOfAnnualLeave + EmployeeNumberOfDaysOfAnnualLeaveInEachMonth
END

2) Create a scheduled job:

1) Expand Sql Server Agent

2) Right Click Jobs --> New Job

3) Give it a name

4) Go to Steps --> New --> Set it to execute your procedure

5) Schedules --> New --> Set it to execute on the first of each month

6) Enable the job

edited Jan 22, 2014 at 13:58

answered Jan 22, 2014 at 13:52

Tom

7,7401 gold badge26 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update SQL table using trigger

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related