Description:

Procedure to delete old working lists:

- older than a certain date OR
- by user

Solution:

Here's a stored procedure that will delete all working lists that are a) older than a specified date AND b) are not stored in a folder beginning with the word 'Archive'. The procedure, hereforth called "The Trasher" will:
1) Accept a date as an argument. Please use 'mm-dd-yyyy' as your format.
2) Disable the working list audit triggers. This is necessary since it would take forever to run otherwise
3) Delete from working list child tables any items from working lists where rec_last_mod_date is BEFORE the input date AND are not in a folder beginning with the word 'Archive' (folder name is not case sensitive)
4) Delete the working list itself from the working_list table
5) Enable the working list triggers
6) Beat a hasty exit.

Any working lists that you want to keep should be moved or copied to a folder that begins with the word 'Archive'. The Trasher will not touch these working lists. Also, please note that there will be no entries in the deleted schema for the working lists that are deleted. If you need to restore a working list, you will need to build it again.

To create the Trasher in your database, copy the attached file. Logon to SQL+ as biotics_dlink and enter:
@ <path where file is located>\trash_wl.sql

You should get the response: 'Procedure created'.

To execute the Trasher:
0) It's nice to notify your users ahead of time that you will be performing this function, so that they can save any old working lists in an 'Archive' folder.
1) Logon to SQL+ as the biotics_user
2) Enter: set serverout on
3) Enter: exec trash_wl('05-31-2006'); This example will delete any working lists older than 5/31/2006 that aren't in an 'Archive'folder.
4) The Trasher will then do its stuff and give you a message when it's finished.

A second procedure has been added that lets you select which working lists to delete by specifying a user_name. This might be handy, for example, to get rid of unwanted lists of former employees. See attachment called "trash_wl_by_user1."