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.



No comments:

Post a Comment

Dear User,

Thank you very much for your kind response