Specfic Date exists in a weekrange

[vituja]

Hi All,

looking for some TSQL that will allow me to see if a date falls within a specific weekrange.

I'm going to be using the code on a trigger and need an event to fire only on the first day of the new year. So if I have a field called weekrange containing "12/30/2002 - 01/05/2003" I want to check if within that range that "01/01/2003" is in it.

This Trigger doesn't work but it is what I would like to have happen. 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 updated

DECLARE @DTE varchar(10)
select @DTE ='01/01/' + convert(char(4),year(left(weekrange,10))) from updated

If (select Approved from UPDATED) = 'Y' and
substring((select Approved from UPDATED),1,10)=@DTE
and (select netid from UPDATED)= @netid


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

 

[vituja]

I tried this trigger but get an error when the trigger fires saying 'Invalid object name UPDATED':

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

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

DECLARE @DTE varchar(10)
select @DTE ='01/01/' + convert(char(4),year(left(weekrange,10))) from updated

If (select Approved from UPDATED) = 'Y' and
convert(datetime,@DTE) between
convert(datetime,substring((select weekrange from UPDATED) ,1,10))
and convert(datetime,substring((select weekrange from UPDATED) ,14,10))

and (select netid from UPDATED)= @netid

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

 

[Kris11]

You are getting an error on "UPDATED" because there is no such such temporary table called "Updated". We have "Deleted and Inseted" not updated. The deleted table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and are transferred to the deleted table.

-Kris

 

[vituja]

Thank you for the help. I didn't know that.


Is there a way to test a trigger. I can not find any error when the trigger is fired except for the data not be inserted.

How can I debug a trigger:
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(left(weekrange,10))) from Inserted

if (select count(*) as cnt from(select distinct weekrange from timesheet_approval_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='vituja' and
weekrange=(select weekrange from Inserted)
) A) = 1

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