What is a one sentence summary of your feature request?
Add a foreign key from SA_FSDLP_Matches to SA_FSAA_Resources with new trigger-based cleanup to automatically handle DLP match deletions when resources are soft-deleted.
Please describe your idea in detail. What is your problem, why do you feel this idea is the best solution, etc.
Problem: Previously, when resources were deleted or mitigated in SDD, related DLP match data wasn’t automatically cleaned up. This required manual execution of FS_SDD_DELETE job which added maintenance overhead.
Solution: Introduced a new FK (SA_FSDLP_Matches ([HOST],[FileId]) → SA_FSAA_Resources ([HOST],[ID])) and created a trigger (TR_SA_FSAA_Resources_UpdateDLP) to automatically delete related rows from SA_FSDLP_MatchHits and SA_FSDLP_Matches when a resource is soft-deleted.
Reasoning: This ensures consistency between resources and their related DLP matches without requiring manual intervention, reduces operational burden, and eliminates risk of stale or orphaned data.
Why best solution: Using a database trigger keeps cleanup logic close to the data, ensuring it runs consistently and immediately without relying on external jobs or scripts.
How do you currently solve the challenges you have by not having this feature?
Manually run the FS_SDD_DELETE job when SDD data has been cleaned up or mitigated.
Upload any other supporting files that you think should be considered in this idea.
3. Create DLP Tables - Fixed.sql (12.8 KB)