TR_GROUPMEMBER_DUPLICATEGROUPMEMBER

CREATE trigger dbo.TR_GROUPMEMBER_DUPLICATEGROUPMEMBER on dbo.GROUPMEMBER after insert, update
as
begin
    if update(GROUPID) or update(MEMBERID)
    begin
        if exists(
            select
                1
            from
                dbo.GROUPMEMBER
                inner join inserted
                    on inserted.GROUPID = GROUPMEMBER.GROUPID
                    and inserted.MEMBERID = GROUPMEMBER.MEMBERID
            group by
                GROUPMEMBER.GROUPID,
                GROUPMEMBER.MEMBERID
            having
                count(1) > 1
            )
        begin
            raiserror('CK_GROUPMEMBER_DUPLICATEGROUPMEMBER', 16, 1);
            rollback;
        end
    end
end