Folder 'Shredder' for v9?

Richard Burt

We have a specific business requirement to completly remove all record of certain folders 6 months after they have been archived. I do this in v7.6 with a 'folder shredder' process which uses a stored procedure called 'ew_delete_folder'. I think I may have got this from a Metastorm employee but I can't remember for certain.

 

Has this procedure been re-built for v9?

 

This is the v7 version. I think it should still work but would like to get a sanity check before trying it out.

 

USE [Metastorm]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[ew_delete_folder] 
            @passedfolderid NVARCHAR(31) 
AS 
DECLARE @folderid varchar(31)
SET @folderid = @passedfolderid

DECLARE @cmd NVARCHAR(1000)
DECLARE @mapname NVARCHAR (31)
DECLARE @tablename NVARCHAR (250)
SET @mapname = (SELECT eMapName FROM eFolder WHERE eFolderID = @folderid) 
SET @tablename = (SELECT eTableName FROM eMap WHERE eMapName = @mapname) 

SET @cmd = 'DELETE FROM ' + @tablename + ' WHERE EFOLDERID = ''' + @folderid + ''''
EXEC(@cmd)

DELETE FROM eFolder WHERE eFolderID = @folderid
DELETE FROM eAttachment WHERE eKey LIKE '%' + CHAR(9) + @folderid  + CHAR(9) + '%'
DELETE FROM eWait WHERE eFolderID = @folderid
DELETE FROM eAssignment WHERE eFolderID = @folderid
DELETE FROM eEvent WHERE eFolderID = @folderid
DELETE FROM eAlertRequest WHERE eFolderID = @folderid
DELETE FROM eAlertGeneratorLock WHERE eFolderID = @folderid
DELETE FROM eLog WHERE eFolderID = @folderid
DELETE FROM eRaisedFlag WHERE eFlagFolder = @folderid

--  Update or remove eAlert table entries for deletion depending on the eServer.eDeletionAlerts setting.

DECLARE @l_DeletionAlert INT

SELECT @l_DeletionAlert = eDeleteDeletionAlerts
  FROM eServer

IF( @l_DeletionAlert = 0 )
BEGIN
UPDATE eAlert
         SET eAlertType  = N'~',
             eAlertMessage = 'Deleted by Administrator',
             eAlertTime  = GETDATE()
       WHERE eFolderID   = @folderid
END
ELSE
BEGIN
DELETE 
        FROM eAlert
       WHERE eFolderID   = @folderid
END


GO

 

 

Thanks,

 

Rick.

Brian Mellert

I see a esp_delete_folder stored procedure in the system (v9.2 anyway, but I presume the other versions as well) which appears to do the same thing, but includes subprocess tables.