how to audit a table (DML actions) with specific conditions (mssql 2014)
Hi, i would like to audit DML operations on a table (deletes mostly) but the problem is, that it needs to answer a specific condition. i need to catch a specific condition of columns (for example, i have a bit column "to_delete" and if its value is 0 i want to catch only those delete statements that deleted records where "to_delete = 0". i am familiar with Database Audit Specifications but it will audit all deletes, and the problem is that in my specific table i have millions of deletes per day so it will be impossible to track the transaction i need. Any Suggestions?
What exactly do you want to audit? I ask because that term is sometimes used loosely for various purposes. If you don't need to know the specific SQL statement but just the row and user, for example, have you considered a delete trigger? Of course, the trigger would fire for every delete, but you could just audit the desired rows by checking the `to_delete` column in the `deleted` virtual table. If you do need the SQL statement, I believe you might be able to get it via the DMVs referencing the current value of @@SPID, but doing a lot in a trigger could prove to be an issue if fired on millions of operations per day. Here's a simple example of a delete trigger: CREATE TRIGGER schema.table_DTR ON schema.table AFTER DELETE AS BEGIN; SET NOCOUNT ON; INSERT audit.deleted_rows SELECT key_id, column1, SYSTEM_USER FROM deleted WHERE to_delete = 0 ; END;
Another option would be to capture queries using extended events. You can apply pretty good filtering so that you only capture DELETE queries and only against the table in question. You're still going to have to deal with a lot of data in the capture, but it's going to be less intrusive than a trigger.