My requirement is to create a database trigger to track all the changes happening on each tables under this database.
I noticed that create database trigger having options to track changes on database level but not on each table level.
Please suggest me.
So I guess what you are looking for is creating audit tables for every DML(update, insert, delete) event in that database, so you can do that like in this example for each table:
what is the correct syntax for creating a database trigger for insert, modify and delete
Or you can use this SP that creates Audit Table and Insert\Update\Delete Triggers for a given table. Just retrieve all the tables in db with a simple script like this, put them in a temp table and loop through that table to create audit table and triggers:
https://gallery.technet.microsoft.com/scriptcenter/Create-Audit-Table-and-5cd69d5d
DECLARE @Table_Name VARCHAR(100)
DECLARE @Schema_Name VARCHAR(100)
SELECT SCHEMA_NAME(schema_id) as SchemaName,name as TableName
INTO #Temp
FROM sys.tables
While (Select Count(*) From #Temp) > 0
Begin
Select Top 1 @Table_Name = TableName From #Temp
Select Top 1 @Schema_Name = SchemaName From #Temp
EXEC GenerateTriggers @Schemaname = @Schema_Name,@Tablename = @Table_Name, @GenerateScriptOnly = 1
Delete #Temp Where TableName= @Table_Name and SchemaName = @Schema_Name
End
