Generate Insert,Update,Delete Trigger for all table of a database in SQL SERVER

When managing databases in SQL Server, ensuring data integrity and tracking changes is crucial. Triggers are powerful tools for automating tasks, logging changes, and maintaining consistent data across tables. This blog demonstrates how to generate Insert, Update, and Delete triggers dynamically for all tables in a database using SQL scripts.

This script allows database administrators and developers to create triggers for audit purposes or business logic enforcement without manually writing them for each table. It also supports a mode to either generate the script for review or execute it directly to create triggers.

SQL Script to Generate Triggers for All Tables

Here’s the SQL script to dynamically generate triggers for every table in a specific schema of a database.

/* 
Parameters 
@Schemaname            - SchemaName to which the table belongs. Default value 'dbo'. 
@DbName                - Database name where audit data will be stored (log table location). 
@Tablename             - TableName for which the triggers will be generated. 
@GenerateScriptOnly    - When passed 1, this will generate the scripts only. 
                         When passed 0, this will create the audit tables and triggers in the current database. 
                         Default value is 1. 
*/

SET NOCOUNT OFF

DECLARE @table NVARCHAR(128)
DECLARE @SQLTrigger NVARCHAR(MAX)
DECLARE @Schemaname SYSNAME = 'dbo' -- schema name
       ,@DbName SYSNAME = 'dbname' -- name of the database in which triggers will be generated
       ,@Tablename SYSNAME
       ,@GenerateScriptOnly BIT = 1 -- 1 for generating scripts only, 0 to create triggers directly.

DECLARE AllTables CURSOR
FOR
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'base table'

OPEN AllTables

FETCH NEXT
FROM AllTables
INTO @table

WHILE @@fetch_status = 0
BEGIN
    SELECT @Tablename = @table

    SELECT @SQLTrigger = '-- =============================================
-- Author:           Programerzone
-- Create date: '+CAST(GETDATE() AS NVARCHAR(256))+'
-- Description: Trigger for ' + @TableName + '
-- =============================================
'

    SELECT @SQLTrigger = @SQLTrigger + 'CREATE TRIGGER TR_' + @Tablename + ' ON dbo.' + @TableName + '
    FOR INSERT, UPDATE, DELETE
    AS
    IF (SELECT COUNT(*) FROM inserted) > 0
    BEGIN
        IF (SELECT COUNT(*) FROM deleted) > 0
        BEGIN
            -- Update!
            INSERT INTO ' + @DbName + '.' + @Schemaname + '.' + @Tablename + ' SELECT *,''Update'' FROM inserted
        END
        ELSE
        BEGIN
            -- Insert!
            INSERT INTO ' + @DbName + '.' + @Schemaname + '.' + @Tablename + ' SELECT *,''Insert'' FROM inserted
        END
    END
    ELSE
    BEGIN
        -- Delete!
        INSERT INTO ' + @DbName + '.' + @Schemaname + '.' + @Tablename + ' SELECT *,''Delete'' FROM deleted
    END'

    IF @GenerateScriptOnly = 1
    BEGIN
        PRINT @SQLTrigger
        PRINT 'GO'
    END
    ELSE
    BEGIN
        PRINT 'Creating Audit Trigger TR_' + @Tablename + ' for ' + @Schemaname + '.' + @Tablename

        EXEC (@SQLTrigger)

        PRINT 'Trigger ' + @Schemaname + '.' + 'TR_' + @Tablename + ' Created successfully'
    END

    FETCH NEXT
    FROM AllTables
    INTO @table
END

CLOSE AllTables
DEALLOCATE AllTables
      

Explanation of the Script

Parameters:

@Schemaname: The schema of the table (default: dbo).

@DbName: The database name where log/audit tables exist or will be created.

@GenerateScriptOnly: If set to 1, it generates the SQL scripts for the triggers. If set to 0, it directly creates the triggers.

Cursor: The script uses a cursor to loop through all the tables in the schema and dynamically create SQL trigger scripts for each table.

Trigger Logic:

Insert Operation: Data from the inserted table is logged into the audit table with an "Insert" operation type.

Update Operation: When records exist in both inserted and deleted tables, it's treated as an update. Both old and new values can be captured.

Delete Operation: Data from the deleted table is logged into the audit table with a "Delete" operation type.

Benefits of Using This Script

1. Automation: Saves time by generating triggers for all tables automatically.

2. Customization: You can modify the script to add specific logic or change audit table structure.

3. Flexibility: Option to generate scripts only or directly create triggers.

Use Cases

Audit Logging: Track all changes made to tables for audit purposes.

Data Backup: Log every change to maintain a history of data changes.

Debugging: Monitor database changes during application testing.

Conclusion

This script is a practical way to automate the creation of triggers across multiple tables in a database. Whether you need triggers for audit logging, data change monitoring, or enforcing business rules, this script offers a flexible solution. By leveraging the @GenerateScriptOnly parameter, you can review the generated SQL before execution or create triggers directly.

Feel free to customize this script according to your needs and ensure you test it in a development environment before applying it to production.