-- sql script to generate trigger for
all existing table
/*
Parameters
@Schemaname - SchemaName to which the table
belongs to. Default value 'dbo'.
@DbName -database name where data need
to be insert(log data store tabale)
@Tablename - TableName for which the procs
needs to be generated.
@GenerateScriptOnly - When passed 1 ,
this will generate the scripts alone..
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 trigger is going to
generate.
,@Tablename SYSNAME
,@GenerateScriptOnly BIT
= 1 -- whether
generate script only or execute 1 for script only,0 for to run 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 succefully'
END
FETCH NEXT
FROM AllTables
INTO @table
END
CLOSE AllTables
DEALLOCATE AllTables