Trigger to update a table - 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 2005 Forums

Transact-SQL (2005)

Trigger to update a table

Author

Topic

mrm23
Posting Yak Master

198 Posts

Posted - 2008-12-03 : 01:21:03

Hi,
I am trying to update a table using a trigger.
whenever the emp_status_item_code of employee changes to 2,
his allocation status in the rpmg_resource_allocation table must also change.

ALTER trigger [dbo].[UpdateEmpStatus]
on [dbo].[EMPLOYEE]
after update
as
begin
declare @empid int
declare @status int
select @status = e.emp_status_item_code
from employee e
where e.emp_seq_no = @empid

if @status = 2

update rpmg_resource_allocations
set rra_status = 2
where emp_seq_no = @empid and rra_updated is null

end

This is not working. can anyone help?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 01:26:36

[code]ALTER trigger [dbo].[UpdateEmpStatus]
on [dbo].[EMPLOYEE]
after update
as
begin

if exists(select 1
from inserted i
where emp_status_item_code =2)
update
set rra_status = 2
from rpmg_resource_allocations rra
inner join inserted i
on i.empid=rra.emp_seq_no
where rra.rra_updated is null

end[/code]

mrm23
Posting Yak Master

198 Posts

Posted - 2008-12-03 : 02:10:51

Thanks a lot. it works.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 02:44:53

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources