000032991 - How to track who deleted a record and when in RSA Archer using a SQL Trigger

1. Perform a SQL Backup and test in a non-prod environment first.
2. From SQL Management Studio, right-click the Archer Instance database and select New Query.
3. Create a new SQL Table to store the data when a Content record is deleted by running the following:

-- Create a table similar to the tblIVContent table's schema 
CREATE TABLE [dbo].[ATBContentDeletedHistory](
            [module_id] [int] NOT NULL,
            [level_id] [int] NOT NULL,
            [content_id] [int] NOT NULL,
            [update_date] [datetime] NULL,
            [update_login] [int] NULL)

4. Create the SQL Trigger when a row is deleted from tblIVContent by running the following:

-- Delete the SQL Trigger if it exists
IF OBJECT_ID('TRG_ArcherContentDeleted') IS NOT NULL 
DROP TRIGGER TRG_ArcherContentDeleted 
GO
 
-- Create the SQL Trigger
CREATE TRIGGER TRG_ArcherContentDeleted
ON dbo.tblIVContent
AFTER DELETE AS
BEGIN
INSERT INTO ATBContentDeletedHistory ([module_id],[level_id],[content_id],[update_date],[update_login])
SELECT [module_id],[level_id],[content_id],[update_date],[update_login] FROM DELETED 
END 
GO

5. To get details from the ATBContentDeletedHistory table, run the following:

SELECT mt.module_name, lt.level_name, atb.content_id, atb.update_date, atb.update_login, u.user_display_name 
FROM ATBContentDeletedHistory atb
JOIN tblUser(NOLOCK) u ON u.user_id = atb.update_login
JOIN tblLevelTranslation(NOLOCK) lt ON lt.level_id = atb.level_id 
JOIN tblModuleTranslation(NOLOCK) mt ON mt.module_id = atb.module_id 
ORDER BY update_date DESC

-- Delete the SQL Trigger if it exists
IF OBJECT_ID('TRG_ArcherContentDeleted') IS NOT NULL 
DROP TRIGGER TRG_ArcherContentDeleted 
GO
 
-- Delete the table if it exists
IF OBJECT_ID('ATBContentDeletedHistory') IS NOT NULL 
DROP TABLE ATBContentDeletedHistory 
GO