asked
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;
Thanks for the answer tom, let me elaborate more on the issue at hand: lets say this is my table, so you can use that as a reference: TABLE [test1] ID [int] NOT NULL, ExpDate [datetime] NOT NULL i want to log all the deletes from test1 WHERE ExpDate = '9999-12-31 00:00:00.000' the problem that i'm having is sporadic, unknown deletes from the table of values that should not be deleted at all (checked against the above statement), so the information i require is everything that will help me identify what end point issued the delete, so i guess that would be, not limited to: SPID, Login, HostName, DBName, command/specific sql query of the delete, sql_text, session_id, program name and etc. the delete trigger crossed my mind, but because im having millions of deletes a day on the table im afraid of the overhead the trigger will create. if there will be no other options then yes, i guess i will have to resort to delete triggers but first i want to explore (if there are any) any sql server engine options that will leave less footprint on the DB.
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.
20 People are following this question.
