TR_GROUPMEMBER_UPDATEISCONSTITUENT

                    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