Database Expert's Microsoft SQL Server 2008 R2:Triggers to monitor DML events on Database Tables.
This Trigger is helpful to monitor DML events on particular database.
1.Create Log Table.
USE [DBA]
GO
/****** Object: Table [dbo].[tTrgLog] Script Date: 10/15/2012 11:19:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tTrgLog](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[ActionId] [varchar](50) NULL CONSTRAINT [DF_tTrgLog_ActionId] DEFAULT (space((0))),
[TableName] [nvarchar](50) NULL CONSTRAINT [DF_tTrgLog_TableName] DEFAULT (space((0))),
[RowOldValue] [nvarchar](max) NULL CONSTRAINT [DF_tTrgLog_RowOldValue] DEFAULT (space((0))),
[RowNewValue] [nvarchar](max) NULL CONSTRAINT [DF_tTrgLog_RowNewValue] DEFAULT (space((0))),
[spID] [nvarchar](50) NULL,
[servername] [nvarchar](50) NULL,
[error] [nvarchar](50) NULL,
[CreatedBy] [nvarchar](50) NULL CONSTRAINT [DF_tTrgLog_CreatedBy] DEFAULT (space((0))),
[CreatedOn] [datetime] NULL,
CONSTRAINT [PK_tTrgLog] PRIMARY KEY CLUSTERED
(
[LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
2.Create Trigger.
/****** Object: Trigger [dbo].[lTrg_tProduct_log] Script Date: 10/15/2012 11:15:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************/
/*
Copyright: 2012 Database Experts
*
* Name : $Archive: lTrg_tProduct_log
* Version : $Revision: 1
* Last Updated : $Modtime: 2012-09-26
* Created By : $Author: Vijay Patel, Bhavesh Thakkar.
* Modified By :
* Script : This trigger will insert Log Entry
* Input: All Data Inserted to Table tProduct
* Version Info:
* No. Author Date Comments
* --- ------ ---- --------
* 1 *
*/
/*****************************************************************************/
CREATE TRIGGER [dbo].[lTrg_tProduct_log]
ON [dbo].[tProduct]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
--Declare the variable
--
-- Check if this is an INSERT, UPDATE or DELETE Action.
--
DECLARE @action as char(1),
@var varchar(max),
@Column_Name varchar(1000),
@Data_Type varchar(1000),
@SQL varchar(MAX),
@SQLQuery varchar(MAX)
DECLARE @TableName varchar(50)
set @TableName = 'tProduct'
IF CURSOR_STATUS('global','db_cursor')>=-1
BEGIN
DEALLOCATE db_cursor
END
DECLARE db_cursor CURSOR FOR SELECT c.name AS column_name,IC.DATA_TYPE
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
Inner Join INFORMATION_SCHEMA.COLUMNS IC
On t.name=IC.TABLE_NAME
and c.name=ic.COLUMN_NAME
Where t.name= @TableName -- 'tProduct'
ORDER BY c.name;
-- Set Action to Insert by default.
SET @action = 'I';
SET @SQL=''
set @SQLQuery=''
IF EXISTS(SELECT * FROM DELETED)
BEGIN
SET @action =
CASE
WHEN EXISTS(SELECT * FROM INSERTED) THEN 'U' -- Set Action to Updated.
ELSE 'D' -- Set Action to Deleted.
END
/**************** START Bhavesh **********************/
/* FOR Update and DELETE */
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @Column_Name ,@Data_Type
WHILE @@FETCH_STATUS = 0
BEGIN
if @Data_Type in ('varchar','char','nvarchar','nchar')
SET @SQL=@SQL+'''['+@Column_Name+']:''+isnull(['+@Column_Name+'],''-'')+ '
else
SET @SQL=@SQL+'''['+@Column_Name+']:''+isnull(cast(['+@Column_Name+'] as varchar(500)),''-'')+'
FETCH NEXT FROM db_cursor INTO @Column_Name,@Data_Type
End
SET @SQL=substring(@SQL,1,len(@SQL)-1)
Select * into #temp1 from deleted
Select * into #tempNew from inserted --- UPDATED
Create Table #temp (COl1 varchar(max))
Create Table #tempNewValue (newvalue varchar(max))
SET @SQLQuery=' insert into #temp SELECT '+@SQL+' [col1] from #temp1'
--Print @SQL
Exec(@SQLQuery)
SET @SQLQuery=' insert into #tempNewValue SELECT ' + @SQL + ' [newvalue] from #tempNew'
Exec(@SQLQuery)
--Select * from #temp
--Print @SQL +'from inserted'
Close db_cursor
Deallocate db_cursor
/**************** END : Bhavesh **********************/
-- Insert into LOG FOR UPATE and DELETE
INSERT INTO tTrgLog ( ActionId, TableName, RowOldValue, RowNewValue, spID ,servername, error, CreatedBy, CreatedOn)
SELECT @action ,@TableName , (Select top 1 COl1 From #temp), (select top 1 newvalue from #tempNewValue) , CAST( @@SPID as nvarchar) , CAST(HOST_NAME() AS VARCHAR(50)),cast( @@error as nvarchar) , (SELECT top 1 ModifiedBy FROM #tempNew) , GETDATE()
FROM deleted
DROP table #temp
Drop table #temp1
DROP TABLE #tempNew
Drop table #tempNewValue
END
ELSE
BEGIN
IF NOT EXISTS(SELECT * FROM INSERTED) RETURN; -- Nothing updated or inserted.
--- Inserted Record Log Entry for INSERT
SET @action = 'I'
/**************** START: Bhavesh **********************/
/* FOR Update and INSERT */
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @Column_Name ,@Data_Type
WHILE @@FETCH_STATUS = 0
BEGIN
if @Data_Type in ('varchar','char','nvarchar','nchar')
SET @SQL=@SQL+'''['+@Column_Name+']:''+isnull(['+@Column_Name+'],''-'')+ '
else
SET @SQL=@SQL+'''['+@Column_Name+']:''+isnull(cast(['+@Column_Name+'] as varchar(500)),''-'')+'
FETCH NEXT FROM db_cursor INTO @Column_Name,@Data_Type
End
SET @SQL=substring(@SQL,1,len(@SQL)-1)
Select * into #temp2 from inserted
Create Table #temp3 (COl1 varchar(max))
SET @SQL=' insert into #temp3 SELECT '+@SQL+' [col1] from #temp2'
-- Print @SQL
Exec(@SQL)
Close db_cursor
Deallocate db_cursor
/**************** END Bhavesh **********************/
-- Insert into LOG FOR INSERT ONLY
INSERT INTO tTrgLog ( ActionId, TableName, RowNewValue, spID,servername, error,CreatedBy,CreatedOn)
SELECT @action ,@TableName , (Select top 1 COl1 From #temp3), CAST( @@SPID as nvarchar) , CAST(HOST_NAME() AS VARCHAR(50)) ,cast( @@error as nvarchar) , CreatedBy,GETDATE() FROM inserted
drop table #temp3
drop table #temp2
END --- END of "Elseif"
END
/****** Object: Trigger [dbo].[lTrg_tWeightRim_log] Script Date: 10/01/2012 11:20:50 ******/
SET ANSI_NULLS ON
/****** Object: Trigger [dbo].[lTrg_tReminder_log] Script Date: 10/11/2012 11:38:33 ******/
SET ANSI_NULLS ON
3.Monitor entry into Log Table.
Comments
Post a Comment
Dear User,
Thank you very much for your kind response