Trigger doesn't fire.

[vituja]

Hi All,

In a continuation to understand triggers, this one listed below does not give me an error, it just doesn't fire.

When the column APPROVED='Y' and WEEKRANGE which has '12/29/2003 - 01/05/2004' the variable DTE='01/01/2004' is within the WEEKRANGE, then fire (run the three SPs).

Why can't this fire???

Any help would be appreciated. Thanks.


CREATE TRIGGER [YearEndProcessing_Mgr] ON [TimeSheet_MGR_TBL]
FOR UPDATE
AS

DECLARE @netid varchar(6)
select @netid = netid from Inserted

DECLARE @DTE varchar(10)

select @DTE ='01/01/' + convert(char(4),year(dateadd(year,1,substring(((select weekrange from Inserted)),14,10))) ) from Inserted

DECLARE @ThisWeek Numeric
set @ThisWeek =(select count(weekrange) from timesheet_MGR_tbl
where convert(datetime,@DTE) between convert(datetime,substring((select weekrange from Inserted),1,10))
and
convert(datetime,substring((select weekrange from Inserted),14,10))and netid=@netid and
weekrange=(select weekrange from Inserted) )

if @ThisWeek=1

begin
exec YearEnd_Accrual_Vacation @DTE, @netid
exec YearEnd_Carryover_Vacation @DTE, @netid
exec YearEnd_Accrual_Personal @DTE, @netid
end

 

[nigelrivett]

You can't tell why a trigger does't fire by looking at the trigger.
Ar you sure it doesn't.
The trigger you have will only cope with single row updates - maybe the update is affecting multiple rows and the value you are getting in the variable is causing the SPs to not do anything.

Create a trace table an insert into it from the trigger so you can trace what is happenning.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.