How? Add a filter to existing trigger..

[archerks]

I would like to add a filter to a trigger but I am not having any luck at getting it to work.

Here is the filter. It uses the Table Absence and column CurrentYear. I don't know the correct SQL terms to use in the formula but what it does is provide a year based on todays date minus 3 months. Example the dates between 4/01/2005 and 3/31/2006 would return the year 2005.

Abs.CurrentYear = Year(today()) - CHOOSE(MONTH(today())<4)

Here is the trigger....

CREATE TRIGGER [UpdateVacUsed] ON [dbo].[Absence]
FOR INSERT, UPDATE
AS
Update Employee
Set Employee.VacUsed = T.VacTaken
from (select EmpNum, sum(VacTaken) VacTaken from Absence Where EmpNum in (select EmpNum from inserted) group by EmpNum) T
Where Employee.EmpNum = T.EmpNum

Thanks, any help is greatly appreciated
David Shaw

 

[amrita418]

Add this code to the where clause in the select stmt.

abs.CurrentYear = year(dateadd(month, -3, getdate()))

PS: Code not tested.

Regards,
AA

 

[archerks]

Thank you very much. With a mimor change that did the trick.

Heres what the change looks like for those interested.

CREATE TRIGGER [UpdateVacUsed] ON [dbo].[Absence]
FOR INSERT, UPDATE
AS
Update Employee
set Employee.VacUsed = T.VacTaken
from (select EmpNum, sum(VacTaken) VacTaken from Absence
where CurrentYear = year(dateadd(month, -3, getdate())) and EmpNum in (select EmpNum from inserted) group by EmpNum) T

Where Employee.EmpNum = T.EmpNum