TR_MEMBER_VALIDMEMBERCHECK

CREATE trigger dbo.TR_MEMBER_VALIDMEMBERCHECK on dbo.MEMBER after insert, update not for replication
as begin
    set nocount on;

    if update(CONSTITUENTID) or update(MEMBERSHIPID) or update(ISDROPPED)
    begin
        if exists (
            select top 1
                1
            from
                dbo.MEMBER
            inner join (
                select distinct MEMBERSHIPID, CONSTITUENTID
                from inserted
                where ISDROPPED = 0
            ) as UPDATEDMEMBERS on UPDATEDMEMBERS.MEMBERSHIPID = MEMBER.MEMBERSHIPID and UPDATEDMEMBERS.CONSTITUENTID = MEMBER.CONSTITUENTID
            where
                MEMBER.ISDROPPED = 0
            group by
                MEMBER.MEMBERSHIPID, MEMBER.CONSTITUENTID
            having
                count(*) > 1
        )
        begin
            raiserror('BBERR_INVALIDMEMBER', 13, 1);
            rollback transaction;
        end

        if exists (
            select
                1
            from
                inserted
            inner join
                dbo.MEMBERSHIP as INSERTEDMEMBERSHIP on INSERTEDMEMBERSHIP.ID = inserted.MEMBERSHIPID
            inner join
                dbo.MEMBERSHIPPROGRAM on MEMBERSHIPPROGRAM.ID = INSERTEDMEMBERSHIP.MEMBERSHIPPROGRAMID
            left outer join
                deleted on deleted.ID = inserted.ID
            where
                inserted.ISDROPPED = 0
                and MEMBERSHIPPROGRAM.ALLOWMULTIPLEMEMBERSHIPS = 0
                and INSERTEDMEMBERSHIP.STATUSCODE <> 1  -- Cancelled

                and exists (
                    select
                        1
                    from
                        dbo.MEMBER
                    inner join
                        dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
                    where
                        MEMBER.CONSTITUENTID = inserted.CONSTITUENTID
                        and MEMBER.MEMBERSHIPID <> inserted.MEMBERSHIPID
                        and MEMBERSHIP.MEMBERSHIPPROGRAMID = INSERTEDMEMBERSHIP.MEMBERSHIPPROGRAMID
                        and MEMBERSHIP.STATUSCODE <> 1  -- Cancelled

                        and MEMBER.ISDROPPED = 0
                )
                and (
                    -- The following ensures that an error isn't raised when an existing member is updated.

                    -- A check is made to verify that the member is either new, or the constituent or membership is changing.

                    deleted.ID is null
                    or inserted.CONSTITUENTID <> deleted.CONSTITUENTID
                    or inserted.MEMBERSHIPID <> deleted.MEMBERSHIPID
                )
        )
        begin
            raiserror('CK_MEMBER_VALIDALLOWMULTIPLEMEMBERSHIPS', 13, 1);
            rollback transaction;
        end
    end
end