CODEWITHSUNDEEP
sql-serverdatabaseloggingdatabase-performancedatabase-security
user2338150
user2338150

Reputation: 479

Global Audit Table in SQL Server

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?

Upvotes: 0

Views: 880

Answers (1)

XAMT
XAMT

Reputation: 1627

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

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

And i strongly preferred option no 1.

Upvotes: 1

Related Questions