Store any transaction in log table SQL Server-CodePudding

I have lots of tables in my SQL Server database. When any action happens, like an Insert, Update, Delete, I want to store the data in a Log table as shown here:

Product table:

| ID  | Name    | Other |
| --- | ------- | ---   |
| 1   | Book    | ...   |
| 2   | Bicycle | ...   |

If any Insert, Update or Delete happens, I want to have something like this:

Log table:

| ID  | RowId   | TableName  | Action |
| --- | ------- | ---------- | ------ |
| 1   | 1       | Product    | Insert |
| 2   | 2       | Product    | Insert |
| 3   | 15      | Category   | Update |
| 4   | 60      | Customer   | Insert |

I'm using Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Express edition with Advanced Services - some features might not be available for me (reference to documentation).

For a single table a trigger is a good idea and it works fine, but what about all tables? As the example shows, Category and Customer tables are other tables in my database.

CodePudding user response：

If your problem is the number of triggers, you should create; then create a job to create the trigger when a new table has been created. The below code checks the tables that have not that kind of triggers using specific formula of naming. In my example tablename plus '_dml_audit'. You can change it anyway.

Set NOCOUNT ON

select Concat (QUOTENAME(sch.name) , '.' , QUOTENAME(tbl.name)) TableNames
into #Tables
    From sys.tables tbl
    INNER JOIN sys.schemas sch on sch.schema_id = tbl.schema_id
    LEFT JOIN sys.triggers trg on tbl.object_id = trg.parent_id and trg.name like tbl.name   '_dml_audit'
    Where trg.object_id is null

GO

exec sp_MSforeachtable 'IF ''?'' IN (Select TableNames FROM #Tables) Begin PRINT ''?'' END'

Drop Table #Tables

For older version that does not support #table use this:

Create Table TempTable (TableNames sysname)
GO
Insert TempTable
SELECT Concat ('[', sch.name , '].[' , tbl.name, ']') TableNames
    From sys.tables tbl
    INNER JOIN sys.schemas sch on sch.schema_id = tbl.schema_id
    LEFT JOIN sys.triggers trg on tbl.object_id = trg.parent_id and trg.name like tbl.name   '_dml_audit'
    Where trg.object_id is null

GO

exec sp_MSforeachtable 'IF ''?'' IN (Select TableNames FROM TempTable) Begin PRINT ''?'' END'
GO
Drop Table TempTable

Replace Trigger Definition with the PRINT statement and Define a job for it.