TR_MAILPREFERENCE_INSERTUPDATE_EMAILVALID

        CREATE trigger TR_MAILPREFERENCE_INSERTUPDATE_EMAILVALID on dbo.MAILPREFERENCE after insert,update not for replication
        as 
        begin
          -- SHL BBIS Bug 334673; Anonymous Email subscribers that are marked as DONOTEMAIL shoudl not be inserted into MAILPREFERENCE

          -- so we need to let the user know but continue to save the ones that are OK to save


          -- Create table to hold distinct email values

          declare @EMAILTABLE table(ID uniqueidentifier, EMAILADDRESS nvarchar(50));
          insert into @EMAILTABLE
          select distinct I.EMAILADDRESSID, E.EMAILADDRESS
          from INSERTED I
          inner join dbo.EMAILADDRESS E on E.ID=I.EMAILADDRESSID
          where E.DONOTEMAIL = 1;

          -- Grab the number of Do Not Email addresses

          declare @DONOTEMAILCOUNT int;
          select @DONOTEMAILCOUNT = count(T.ID)
          from @EMAILTABLE T

          if @DONOTEMAILCOUNT > 0
          begin
            declare @ERRORMSG VARCHAR(1000);    

            -- If there is more than one email marked Do Not Email, we list them out with commas

            if @DONOTEMAILCOUNT > 1
              begin
              -- First, get the emails that are marked DONOTEMAIL

              declare @DONOTEMAILS varchar(500)  
              select @DONOTEMAILS = coalesce(@DONOTEMAILS +', ', '') + E.EmailAddress from @EMAILTABLE E

              -- Second, create the error message

        set @ERRORMSG = 'The process is complete. The following email addresses matched constituent records in Blackbaud CRM but cannot be used because they are marked ?Do not email?: ' + @DONOTEMAILS;
            end
            -- If there is only one email marked Do Not Email, we just display that email address

            else
            begin
              declare @DONOTEMAIL varchar(50);
              select @DONOTEMAIL = E.EmailAddress from @EMAILTABLE E

              set @ERRORMSG = @DONOTEMAIL + ' is currently marked ?Do not email?.';
            end

            -- Third, delete the recently inserted emails that are marked DONOTEMAIL

            delete M
            from dbo.MAILPREFERENCE M
            inner join dbo.EMAILADDRESS E on E.CONSTITUENTID = M.CONSTITUENTID
            where E.DONOTEMAIL = 1;

            -- Last, show the error message to let em know

            -- ROLLBACK TRAN

            raiserror(@ERRORMSG,13,1);
          end
        end