CREATE trigger [dbo].[TR_GROUPMEMBER_UPDATEISCONSTITUENT]
on [dbo].[GROUPMEMBER]
after insert, update
as begin
if (update(GROUPID) or update(MEMBERID))
begin
-- Make sure the household is marked as a constituent,
-- if any member is a constituent
update dbo.CONSTITUENT
set CONSTITUENT.ISCONSTITUENT = 1,
CONSTITUENT.CHANGEDBYID = inserted.CHANGEDBYID,
CONSTITUENT.DATECHANGED = GetDate()
from dbo.CONSTITUENT -- Group
inner join inserted
on CONSTITUENT.ID = inserted.GROUPID
inner join dbo.GROUPDATA
on (GROUPDATA.ID = inserted.GROUPID)
inner join CONSTITUENT as CONSTITUENT_MEMBER
on (CONSTITUENT_MEMBER.ID = inserted.MEMBERID)
where (GROUPDATA.GROUPTYPECODE = 0) and -- Group is a household
(CONSTITUENT.ISCONSTITUENT = 0) and -- Household is NOT a constituent
(CONSTITUENT_MEMBER.ISCONSTITUENT = 1) -- New member is a constituent
-- Make sure to mark the member as a constituent,
-- if the group is NOT a household.
update dbo.CONSTITUENT
set CONSTITUENT.ISCONSTITUENT = 1,
CONSTITUENT.CHANGEDBYID = inserted.CHANGEDBYID,
CONSTITUENT.DATECHANGED = GetDate()
from dbo.CONSTITUENT
inner join inserted
on CONSTITUENT.ID = inserted.MEMBERID
inner join dbo.GROUPDATA
on (GROUPDATA.ID = inserted.GROUPID)
inner join CONSTITUENT as CONSTITUENT_GROUP
on (CONSTITUENT_GROUP.ID = inserted.GROUPID)
where (GROUPDATA.GROUPTYPECODE != 0) and -- Group is NOT a household
(CONSTITUENT_GROUP.ISCONSTITUENT = 1) and -- Group is a constituent
(CONSTITUENT.ISCONSTITUENT = 0) -- New member is NOT a constituent
end
end
|
Trigger Definition
