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)
 help with update trigger

Author  Topic 

blackX
Posting Yak Master

102 Posts

Posted - 2008-01-28 : 13:49:09
I need a trigger that updates a column (secondarymarketgroup) with a value from another table (location.name) when prestnstatus is updated to S,H,or OH if secondarymarketgroup is blank or null. Can anyone help. Here is my update statement I run to get the values I want, but I want it to happen on change of prestnstatus

update member
set secondarymarketgroup=(select name from location where member.primarymarklocation_id=location.location_id) where secondarymarketgroup<>'' and prestnstatus in ('S','OH','H')

I know this appears to be stupid and redundant but there is a valid necessity for this. Please help!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-28 : 14:35:33
I just stuck your statement (slightly re-arranged) into a CREATE TRIGGER statement

NOTE:
You need to change the JOIN Criteria to use whatever the Primary Key Columns are for [Member]


if object_id('tr_U_Member') > 0
drop trigger tr_U_Member
go

create trigger tr_U_Member on dbo.Member AFTER UPDATE
as
begin
update m set
m.secondarymarketgroup = l.[name]
from inserted i
inner join member m on m.<!!PRIMARMYKEY!!> = i.<!!m.PKcolumns!!>
inner join location l on l.location_id = m.primarymarklocation_id
where isNull(secondarymarketgroup, '') <> ''
and prestnstatus in ('S','OH','H')
End
go


Be One with the Optimizer
TG
Go to Top of Page

blackX
Posting Yak Master

102 Posts

Posted - 2008-01-28 : 18:17:57
thanks
Go to Top of Page
   

- Advertisement -