TR_APPEALMAILINGSETUPLETTER_DELETE

CREATE trigger dbo.TR_APPEALMAILINGSETUPLETTER_DELETE on dbo.APPEALMAILINGSETUPLETTER after delete not for replication
as begin
  set nocount on;

  declare @CHANGEAGENTID uniqueidentifier;    
  declare @EMAILSEGMENTID uniqueidentifier;
  declare @MAILSEGMENTID uniqueidentifier;
  declare @IDSETREGISTERID uniqueidentifier;
  declare @NETCOMMUNITYTEMPLATEID int;

  if (select count(ID) from DELETED) = 1
    begin
      select
        @EMAILSEGMENTID = DELETED.EMAILSEGMENTID,
        @MAILSEGMENTID = DELETED.MAILSEGMENTID,
        @IDSETREGISTERID = DELETED.CANNEDSELECTIONIDSETREGISTERID,
        @NETCOMMUNITYTEMPLATEID = coalesce(MKTPACKAGE.NETCOMMUNITYTEMPLATEID, 0),
        @CHANGEAGENTID = DELETED.CHANGEDBYID
      from DELETED
      left join dbo.MKTPACKAGE
        on DELETED.EMAILPACKAGEID = MKTPACKAGE.ID;

      if @IDSETREGISTERID is not null
        begin
          -- Delete the segment selection records associated with the IDSetRegister

          delete dbo.MKTSEGMENTSELECTION where SEGMENTID in (@EMAILSEGMENTID, @MAILSEGMENTID) and SELECTIONID = @IDSETREGISTERID;

          exec dbo.[USP_IDSETREGISTER_DELETEBYID_WITHCHANGEAGENTID] @IDSETREGISTERID, @CHANGEAGENTID;
        end

      if @NETCOMMUNITYTEMPLATEID <> 0
        exec dbo.[spDelete_EmailTemplate] @NETCOMMUNITYTEMPLATEID, 0;
    end

  else
    begin
      declare DELETEDCURSOR cursor local fast_forward for
        select
          DELETED.EMAILSEGMENTID,
          DELETED.MAILSEGMENTID,
          DELETED.CANNEDSELECTIONIDSETREGISTERID,
          coalesce(MKTPACKAGE.NETCOMMUNITYTEMPLATEID, 0),
          DELETED.CHANGEDBYID
        from DELETED
        left join dbo.MKTPACKAGE
          on DELETED.EMAILPACKAGEID = MKTPACKAGE.ID;

      open DELETEDCURSOR;
      fetch next from DELETEDCURSOR into @EMAILSEGMENTID, @MAILSEGMENTID, @IDSETREGISTERID, @NETCOMMUNITYTEMPLATEID, @CHANGEAGENTID;

      while (@@FETCH_STATUS = 0)
        begin
          if @IDSETREGISTERID is not null
            begin
              -- Delete the segment selection records associated with the IDSetRegister

              delete dbo.MKTSEGMENTSELECTION where SEGMENTID in (@EMAILSEGMENTID, @MAILSEGMENTID) and SELECTIONID = @IDSETREGISTERID;

              exec dbo.[USP_IDSETREGISTER_DELETEBYID_WITHCHANGEAGENTID] @IDSETREGISTERID, @CHANGEAGENTID;
            end 

          if @NETCOMMUNITYTEMPLATEID <> 0
            exec dbo.[spDelete_EmailTemplate] @NETCOMMUNITYTEMPLATEID, 0;

          fetch next from DELETEDCURSOR into @EMAILSEGMENTID, @MAILSEGMENTID, @IDSETREGISTERID, @NETCOMMUNITYTEMPLATEID, @CHANGEAGENTID;
        end

      close DELETEDCURSOR;
      deallocate DELETEDCURSOR;
    end
end