Firing triggers for Update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Firing triggers for Update

Author

Topic

nimmie82
Starting Member

4 Posts

Posted - 2005-07-19 : 02:07:05

I have written a trigger. it has to be fired while update. But i'm not getting any response. Please check the commands...

CREATE trigger trigger_efficiency
on proj_progress
for update,insert
as
declare @empid varchar(25),@status int,@approve char(10),@elementid varchar(25)
declare @startdt datetime,@deadline datetime

select @empid=(select emp_id from inserted)
select @status =(select status from inserted)
select @approve =(select approve from inserted)
select @elementid =(select approve from inserted)
if @status=100 and @approve='no'
begin
select @startdt= startdt from emp_project where emp_id=@empid and task_assigned=@elementid
select @deadline=deadline from emp_project where emp_id=@empid and task_assigned=@elementid

if datediff(d,@deadline,getdate())=0
begin
if exists (select * from efficiency where emp_id=@empid)
begin
update efficiency
set ontime = ontime+1
where emp_id = @empid
end
else
begin
insert into efficiency values(@empid,1,0,0)
end
end
else if datediff(d,@deadline,getdate())>0
begin
if exists (select * from efficiency where emp_id=@empid)
begin
update efficiency
set aftertime = aftertime+1
where emp_id = @empid
end
else
begin
insert into efficiency values(@empid,0,0,1)
end
end

else if datediff(d,@deadline,getdate()) < 0
begin
if exists (select * from efficiency where emp_id=@empid)
begin
update efficiency
set beforetime = beforetime+1
where emp_id = @empid
end
else
begin
insert into efficiency values(@empid,0,1,0)
end
end
end

Thast my trigger. when i fire a update statemetn to the tablae progress like this....i'm not getting a record in the efficiency table..

update proj_progress
set status = 100
where emp_id='emp-01' and element_id='p1m1c1e1'

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-07-19 : 02:49:44

funny that this trigger is called trigger_efficiency.

Anyway, it looks like you might be updating more than one row at a time. you are setting variables to values selected from INSERTED, if you insert or update more than one row then that variable assignment will fail.

-ec

nimmie82
Starting Member

4 Posts

Posted - 2005-07-19 : 03:05:05

I'm updating a single row, soon after that i checked the rows in the efficiency table...it's empty.

In case, if i'm writng only for update how shall i get the updated values like we are writing select this from inserted.Is there any solution.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources