question

neomerdien avatar image
neomerdien 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?
sql server 2014auditdml
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab avatar image
Tom Staab answered
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;
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.