June 16, 2009 at 7:03 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 16, 2009 at 7:08 am
Hello,
Are you using an SP to perform the original Insert? Personally, I would put this Business Logic in the SP rather than a Trigger, where it can be “hidden” when people have to maintain the application at a later date.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
June 16, 2009 at 7:11 am
hi
i need to use only trigger
June 16, 2009 at 7:20 am
hi
i need to use trigger
June 16, 2009 at 7:30 am
You can use an instead of trigger and change the value of the column according to the original value that the user used. I suggest that you'll read BOL (Books On Line – The help file that comes with SQL Server) on the subjects " INSTEAD OF INSERT Triggers" and " Using the inserted and deleted Tables"
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 17, 2009 at 5:20 am
no need to add duplicate posts if you didn't get the answer you were after;same question was asked here:http://www.sqlservercentral.com/Forums/Topic735615-145-1.aspx
the more information you provide, the better we can help you.
show us the CREATE TABLE statement of your attendance table.
show us what you've tried so far. remember we are not sitting at your desk...what seems obvious to you as you look at the data and database is not available to us on the other side of the internet.
Lowell
June 18, 2009 at 9:24 am
create or replace trigger atten_emp
after delete or insert update on attendance_table
for each row
begin
update into atten_emp(emp_id,emp_name,login_time,logout_time,login2_time,logout2_time)
values(user,sysdate,:emp_id,:emp_name,:login_time,logout_time:=1pm,login2_time,logout2_time);
end;
/
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply