Database Monitoring (Transactions Operations Log)

Have you ever wanted to monitor one of your tables transaction for non GP tables that couldn’t be monitored by Activity Tracking? I been in a situation where the customer requested to log ALL transactions performed on a third party software, to achieve this you will have two options, one to use SQL Server Profiler to trace your data based on predefined filters and direct the results to an SQL table, which will be stopped if the profiler closed, or your other option is to use SQL triggers to perform insert operation on a monitoring table I called “TransactionsLog”:

Now the tough part is to create triggers on your tables, each table will need to have 3 triggers, one to monitor “Insert”, and one to monitor “Update” and the last one to monitor “Delete”.

Having a big number of tables will make such task a nightmare! Where I had to write the script generator below that generates those triggers for you! It writes you the needed triggers including the primary key field –if it was single – along with the timestamp of the transaction, below the complete script:

/*Transactions Log Table Creation:

Created By: Mohammad R. Daoud

http://mohdaoud.blogspot.com

April 26, 2010

Make sure to have a complete backup on the databases you are running this script on, in addition, running those scripts is your own risk.

*/

 

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TransactionsLog]') AND type in (N'U'))
DROP TABLE [dbo].[TransactionsLog]
GO

CREATE TABLE [dbo].[TransactionsLog](
    [TransactionID] [int] NOT NULL,
    [Type] [varchar](500) NOT NULL CONSTRAINT [DF_LogTable_Type]  DEFAULT (''),
    [TrxDateTime] [datetime] NOT NULL CONSTRAINT [DF_LogTable_TrxDateTime]  DEFAULT (''),
    [TrxDescription] [varchar](5000) NOT NULL CONSTRAINT [DF_LogTable_TrxDescription]  DEFAULT (''),
    [Notes] [varchar](500) NOT NULL CONSTRAINT [DF_LogTable_Notes]  DEFAULT (''),
    [TrxNumber] [int] NOT NULL,
    [RowID] [int] IDENTITY(1,1) NOT NULL,
    [RowUser] [varchar](500) NOT NULL CONSTRAINT [DF_LogTable_RowUser]  DEFAULT (suser_sname()),
    [RowDate] [datetime] NOT NULL CONSTRAINT [DF_LogTable_RowDate]  DEFAULT (getdate()),
CONSTRAINT [PK_LogTable] PRIMARY KEY CLUSTERED
(
    [TransactionID] ASC
))
GO

 

/* Starting Script */

DECLARE @Operation varchar(500)
DECLARE    @TableName varchar(128)
DECLARE @PrimaryKey varchar(500)

DECLARE TriggersCur CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME = ‘GL00100’ OR TABLE_NAME = ‘GL00105’

 

/*Remove Above Filter To Monitor the complete Database, note that this will create triggers on all your database tables, please make sure to have a FULL DATABASE Backup before adding the triggers */

OPEN TriggersCur
FETCH NEXT FROM TriggersCur INTO @TableName

WHILE @@FETCH_Status = 0
BEGIN
SELECT    @PrimaryKey = c.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,        INFORMATION_SCHEMA.KEY_COLUMN_USAGE c WHERE pk.TABLE_NAME = @TableName AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND c.TABLE_NAME = pk.TABLE_NAME AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
SET @Operation = 'INSERT Operation'
PRINT ' IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[' + @TableName + 'INSERT]''))'
PRINT ' DROP TRIGGER [dbo].[' + @TableName + 'INSERT]'
PRINT ' GO '

PRINT ' CREATE TRIGGER ' + @TableName + 'INSERT ON ' + @TableName + ' FOR INSERT AS'
PRINT ' DECLARE @LOGID INT'
PRINT ' DECLARE @TransactionID INT'
PRINT ' SELECT @TransactionID = ' + @PrimaryKey + ' FROM INSERTED'
PRINT ' SELECT @LOGID = ISNULL(MAX(TransactionID),0) + 1 FROM TransactionsLog'
PRINT ' INSERT INTO [TransactionsLog]'
PRINT '            ([TransactionID]'
PRINT '            ,[Type]'
PRINT '            ,[TrxDateTime]'
PRINT '            ,[TrxDescription]'
PRINT '            ,[Notes]'
PRINT '            ,[TrxNumber])'
PRINT '      VALUES'
PRINT '            (@LogID '
PRINT '            ,''' + @Operation + ''''
PRINT '            ,''' + CONVERT(VARCHAR(500), GETDATE()) + ''''
PRINT '            ,'''+ @TableName + ''''
PRINT '            ,'''+ ''''
PRINT '            , CONVERT(VARCHAR(500), @TransactionID) )'
PRINT ' GO '

SET @Operation = 'UPDATE Operation'

PRINT ' IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[' + @TableName + 'UPDATE]''))'
PRINT ' DROP TRIGGER [dbo].[' + @TableName + 'UPDATE]'
PRINT ' GO '

PRINT ' CREATE TRIGGER ' + @TableName + 'UPDATE ON ' + @TableName + ' FOR UPDATE AS'
PRINT ' DECLARE @LOGID INT'
PRINT ' DECLARE @TransactionID INT'
PRINT ' SELECT @TransactionID = ' + @PrimaryKey + ' FROM INSERTED'
PRINT ' SELECT @LOGID = ISNULL(MAX(TransactionID),0) + 1 FROM TransactionsLog'
PRINT ' INSERT INTO [TransactionsLog]'
PRINT '            ([TransactionID]'
PRINT '            ,[Type]'
PRINT '            ,[TrxDateTime]'
PRINT '            ,[TrxDescription]'
PRINT '            ,[Notes]'
PRINT '            ,[TrxNumber])'
PRINT '      VALUES'
PRINT '            (@LogID '
PRINT '            ,''' + @Operation + ''''
PRINT '            ,''' + CONVERT(VARCHAR(500), GETDATE()) + ''''
PRINT '            ,'''+ @TableName + ''''
PRINT '            ,'''+ ''''
PRINT '            , CONVERT(VARCHAR(500), @TransactionID) )'
PRINT ' GO '

SET @Operation = 'DELETE Operation'

PRINT ' IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[' + @TableName + 'DELETE]''))'
PRINT ' DROP TRIGGER [dbo].[' + @TableName + 'DELETE]'
PRINT ' GO '

PRINT ' CREATE TRIGGER ' + @TableName + 'DELETE ON ' + @TableName + ' FOR DELETE AS'
PRINT ' DECLARE @LOGID INT'
PRINT ' DECLARE @TransactionID INT'
PRINT ' SELECT @TransactionID = ' + @PrimaryKey + ' FROM DELETED'
PRINT ' SELECT @LOGID = ISNULL(MAX(TransactionID),0) + 1 FROM TransactionsLog'
PRINT ' INSERT INTO [TransactionsLog]'
PRINT '            ([TransactionID]'
PRINT '            ,[Type]'
PRINT '            ,[TrxDateTime]'
PRINT '            ,[TrxDescription]'
PRINT '            ,[Notes]'
PRINT '            ,[TrxNumber])'
PRINT '      VALUES'
PRINT '            (@LogID '
PRINT '            ,''' + @Operation + ''''
PRINT '            ,''' + CONVERT(VARCHAR(500), GETDATE()) + ''''
PRINT '            ,'''+ @TableName + ''''
PRINT '            ,'''+ ''''
PRINT '            , CONVERT(VARCHAR(500), @TransactionID) )'
PRINT ' GO '

FETCH NEXT FROM TriggersCur INTO @TableName
END
CLOSE TriggersCur
DEALLOCATE TriggersCur

Hope that this helps!

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com