CODEWITHSUNDEEP
sqldatabasetriggersddl-trigger
hp43
hp43

Reputation: 375

Logging changes made to SQL database

I have an sql database with tables, fields, and indices that is constantly worked on and revised. I would like to be able to log each time any changes are made to the content, including the old version of the specific table/field data that was manipulated, the new version, date, and user who made these changes. Having an output format in the view of a table with each of these 4 things as columns would be ideal.

After looking into how this would be done, it seems that using triggers is a good way to go. However, I have not written a trigger or used SQL before. How would I go about implementing this?

I am using VS2008, c#. Obviously, my question is very broad right now as I've only got the concept in my mind, with no knowledge of how to write the code to do it.

Upvotes: 1

Views: 646

Answers (1)

Michael Fredrickson
Michael Fredrickson

Reputation: 37378

Assuming Sql Server - here's an example for creating a DDL trigger, based on this article.

First, create a table to hold the logs...

CREATE TABLE [dbo].[ChangeLog](
    [LogId] [int] IDENTITY(1,1) NOT NULL,
    [DatabaseName] [varchar](256) NOT NULL,
    [EventType] [varchar](50) NOT NULL,
    [ObjectName] [varchar](256) NOT NULL,
    [ObjectType] [varchar](25) NOT NULL,
    [SqlCommand] [varchar](max) NOT NULL,
    [EventDate] [datetime] NOT NULL CONSTRAINT 
        [DF_EventsLog_EventDate]  DEFAULT (getdate()),
    [LoginName] [varchar](256) NOT NULL,
    [UserName] [varchar](256) NULL,
    CONSTRAINT [PK_ChangeLog] PRIMARY KEY CLUSTERED ([LogId] ASC)
)

Next, create the trigger...

CREATE TRIGGER [TrgDDLChangeLog]
ON DATABASE
FOR  

    CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
    CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
    CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
    CREATE_VIEW, ALTER_VIEW, DROP_VIEW,
    CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
    CREATE_INDEX, ALTER_INDEX, DROP_INDEX

AS

DECLARE @Event XML
SET @Event = EVENTDATA()

INSERT INTO dbo.ChangeLog
(
    DatabaseName, 
    EventType, 
    ObjectName, 
    ObjectType, 
    SQLCommand, 
    LoginName,
    UserName
)
VALUES
(
    @Event.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(256)'),
    @Event.value('(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(50)'), 
    @Event.value('(/EVENT_INSTANCE/ObjectName)[1]', 'VARCHAR(256)'), 
    @Event.value('(/EVENT_INSTANCE/ObjectType)[1]', 'VARCHAR(25)'), 
    @Event.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'VARCHAR(max)'), 
    @Event.value('(/EVENT_INSTANCE/LoginName)[1]', 'VARCHAR(256)'),
    @Event.value('(/EVENT_INSTANCE/UserName)[1]', 'VARCHAR(256)')
)

Upvotes: 1

Related Questions