CODEWITHSUNDEEP
sqlsql-server-2008triggers
Mohammed
Mohammed

Reputation: 203

Update SQL table using trigger

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

Upvotes: 0

Views: 76

Answers (1)

Tom
Tom

Reputation: 7740

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

Upvotes: 1

Related Questions