Purge Orphaned Processes Trigger

Overview

This utility script removes orphaned process records from the PROCESSES table by deleting entries that reference non-existent employees. It performs a simple cleanup operation to maintain referential integrity between the PROCESSES table and the EMPLOYEES table, ensuring that all process records have valid employee references.

Parameters

This script has no input parameters - it automatically identifies and removes all orphaned process records.

Data Components

The script performs a single cleanup operation:

1. PROCESSES Table - Removes records where filekey doesn't exist in EMPLOYEES table

The cleanup targets process records that reference employees who have been deleted or purged from the system.

Output Format

The script returns the number of orphaned process records that were deleted:

Technical Implementation

The script uses:

• Simple DELETE statement with NOT IN subquery
• Referential integrity check against EMPLOYEES table
• Direct execution without loops or cursors
• Row count return using @@ROWCOUNT

Processing Logic

1. Orphan Identification: Find PROCESSES records where filekey NOT IN EMPLOYEES
2. Direct Deletion: Remove all orphaned records in single operation
3. Count Return: Report number of deleted records

Safety Features

• Simple Operation: Single DELETE statement reduces complexity
• Reference Integrity: Only removes records with invalid employee references
• Immediate Execution: No batch processing or complex logic
• Clear Reporting: Returns exact count of deleted records

Notes

• Simple Cleanup: Straightforward orphaned record removal
• Process Focus: Only affects the PROCESSES table
• Referential Integrity: Ensures all process records have valid employee references
• Single Operation: Completes in one DELETE statement
• Safe Execution: Only removes records with invalid references
• Maintenance Tool: Useful for regular database cleanup
• Performance Friendly: Simple operation with minimal system impact
• Run this script as part of regular database maintenance
• Execute after employee deletion or purge operations
• Monitor the count to understand the scope of orphaned data
• Consider running during maintenance windows for consistency
• Use this script to maintain clean process data
• Verify that orphaned processes should be completely removed
• Consider the impact on process tracking and reporting

T-SQL

/****** Object:  Trigger [dbo].[Purge_Orphaned_Processes]    Script Date: 6/18/2015 11:03:25 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create TRIGGER [dbo].[Purge_Orphaned_Processes]
   ON  [dbo].[SessionState]
   AFTER Insert, UPDATE
AS 
BEGIN
DELETE FROM SESSIONSTATE WHERE LastUPDATED < DATEADD(HH, -2, GETDATE())
 
END

Content Inventory

• Doc File: content/docs/utilities/purge_orphaned_processes_trigger.mdx
• SQL Script: SQL/utilities/Purge_Orphaned_processes_Trigger.sql