TR_MAILPREFERENCE_INSERTUPDATE_ADDRESSVALID

        CREATE trigger TR_MAILPREFERENCE_INSERTUPDATE_ADDRESSVALID on dbo.MAILPREFERENCE after insert,update not for replication
        as begin

          if exists(
            select i.ID
            from INSERTED i
              inner join ADDRESS a on a.ID=i.ADDRESSID
            where a.DONOTMAIL=1
          )
          begin
            ROLLBACK TRAN
            raiserror('Cannot save a mail preference that refers to an invalid address.',13,1)
          end

        end