June 18, 2009 at 2:20 am
tables:
--------------
emp_att_tab(emp_id,in_time,att_date,shift_id,lunch_out_time,lunch_in_time,id)
shift_mas_tab(shift_id,lunch_start_date)
wht ever we inserted lunch start time in shift mas_tab,when we trying to update the attendance tab while entering the lunch out time shouldbe
greater than lunch_starttime and update the lunch outtime in attendance tab
i tried here the outtime is not updating in attendance table
create trigger lunctout_time on emp_att_tab
instead of update
as
begin
declare @b-2 int,@c datetime,@shift_id int
DECLARE @emp_id int,@lunch_start_time datetime,@lunch_out_time datetime,@in_time datetime,@id int
set @b-2=(select shift_id from emp_att_tab where id=(select max(id) from emp_att_tab where emp_id=@emp_id))
set @c=(select lunch_start_time from shift_mas_tab where shift_id=@b )
if (@lunch_out_time>=@c )
begin
update emp_att_tab set lunch_out_time=@lunch_out_time where id=
(select max(id) from emp_att_tab where emp_id=@emp_id)
raiserror ('u should allow here',10,1)
END
else
raiserror ('u should not allow here',10,1)
END
June 18, 2009 at 4:43 am
That trigger is written assuming there's only one row in the inserted/deleted tables. If more than one row was updated, the trigger fires once and there'll be more than one row in those tables
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply