Issue:
You receive the following message on the Auditor Generic Information Report when you close Auditor.
Trigger RSAS_TEST_ALLOC_DELETE has no corresponding SQL Table audit.
Resolution:
This message indicates that there is a trigger out there that was created, but there is no longer a SQL Table audit defined for it. This can happen if a backup is restored for one database into another database. For instance, when restoring a live company into test company.
When restoring a backup of one database (and all of its triggers) into some other database, triggers from the one database will copy over. If those triggers are no longer associated with an audit, such as an Auditor SQL Table audit, it results in orphaned triggers. This message is letting you know you have an orphaned/stranded trigger.
You will need to identify this trigger in the database that it’s in now. To do this, please open SQL management Studio and run the following script against the database. This script will only list the orphaned trigger(s), it will not remove it. You will need to manually remove the trigger(s).
DECLARE @DbName AS VARCHAR(255), @SQL as VARCHAR(1000)
DECLARE DB_cursor CURSOR FOR SELECT name from master..sysdatabases
OPEN DB_cursor
FETCH NEXT FROM DB_cursor INTO @DbName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @DbName = LTRIM(RTRIM(@DbName));
SELECT @SQL =
‘SELECT * FROM ‘ + @DbName + ‘..sysobjects WHERE (name like ”RSAS%”)’ +
‘ AND (name not like ”RSAS_’ + @DbName + ‘%”) ‘ +
‘ AND (type = ”TR”) ‘;
EXEC (@SQL);
FETCH NEXT FROM DB_cursor INTO @DbName
END
CLOSE DB_cursor
DEALLOCATE DB_cursor