TR_APPEALMAILINGSETUPLETTER_DELETE (Trigger)

        
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