SQL Audit Part 6: Creating The Trigger

So, now we have the database tables created for the audit and the archive, now we need to create the trigger to perform the audit. Now the code for this, based on what I have done to date, is what you would expect.

Set @sql = '';
Set @sql = @sql + N'IF EXISTS (SELECT name FROM sysobjects
WHERE name = ''adt_tgr_'+@auditTableName+''' AND type = ''TR'')
DROP TRIGGER adt_tgr_'+@auditTableName
SET @sql = 'EXEC '+cast(@databaseName as nvarchar(255))+'.sys.sp_executesql N'''+REPLACE(@sql,'''','''''')+'''';
exec sp_executesql @sql;

However there is this part that is the check for the existing trigger. We cant drop a trigger that doesn't exist, but where it does exist we want to drop it so that we recreate it. It is pretty basic, but gets the job done, and also shows the use of the sp_executesql in the source database.

With this in place, the audit is now running.  Any insert, update or delete command on the source database table will create the audit record in the audit table.  But there are still 2 things missing from the original brief, to be able to maintain the structures of the audit and archive tables at the database level, as well as the ability to archive the audit records after a set time so that we don't keep records to the database we don't need.  And these will be covered in the next 2 posts.