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”:

image

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, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Advertisements

About Mohammad R. Daoud
Mohammad R. Daoud has been working as a Microsoft Dynamics GP consultant since 2004. His career path started with version 7.5; he studied every single tip of the application technicalities and did allot of successful implementations that includes functional consultations, analysis, and custom development projects. He holds a graduate degree in Computer Science and currently he is pursuing MBA degree in Accounting. In January 2007 he was nominated to the Microsoft Most Valuable Professional (MVP) certificate and was certified in April 2008 due to his online contributions in the Dynamics Community (Dynamics GP Newsgroups, Forums, User Groups and his blog: http://mohdaoud.blogspot.com). Worth to mention that he was also listed as one of the Microsoft Dynamics Top 100 Most Influential People in 2009 by DynamicsWorld. And more... • Demonstrated Passion for MS Products through participating in beta testing for some product versions, an effective member of Microsoft Connect. • Talented in securing strong high quality relationships with premier customers, partners and sales teams passionately and profitably. • Dedicated to meeting the expectations and requirements of internal and/or external customers • Problem Solver – Uses systematic approach to uncover true problem(s) and practical solution and can marshal resources to solve problem! • Proven record of effective account management, including Account Planning, Opportunity Management, and Business Management Excellence and working as part of a larger virtual team • Successful in delivering high quality technical engagements in the enterprise Dynamics GP implementations area according to customer requirements. • Dedicated, Committed, Self Motivated, Creative and highly focused with excellent communication, Negotiation, presentation, Active Listening and Objection Handling skills. • Experienced in Profitable researches and studies including white papers and technical presentations.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: