Global Audit Table in SQL Server

Question

While looking at some tables in my schema, it occured to me if I could create a global audit table, which might contain some information on DML entries on all tables. I'd like to store 'Table name', 'Modifier', 'Row Update/Insert/Delete query', 'query result', 'Modifying Instance Information: sql-client, session-info(?)', performance items, timestamp, etc. I could also limit this table-size by limiting number of rows through a insert trigger on this table through rowcount. Is this construct allowed? Is this a bad idea? What is the fastest it could get? What more problems does construct like this present? Basically, is direct logging into database a bad idea?

score 1 · Accepted Answer · answered Dec 24 '19 at 12:54

1

Logging DML with database level trigger on big data and bulk insert/update/delete have a performance issue, so there is three other option :

SQL server Change Tracking : more info
Build-in data changes function (CDC) : more info
Database level Audit Log : more info

And i strongly preferred option no 1.

answered Dec 24 '19 at 12:54

XAMT

1,515
2
11
31

1

Thanks for the answer, I had a bike crash the evening later, so I couldn't get to see it. I shall read about the respective topics. – user2338150 Dec 27 '19 at 06:11
@user2338150; that's ok – XAMT Dec 27 '19 at 07:11

Global Audit Table in SQL Server

1 Answers1