SQL serveur - Audit tableau malheurs - Stack Overflow

I am trying to make an Audit table that records pretty much everything in the system. I was going to use triggers, but I want to record things like searches, etc and I always want to record the Action-er (the user that performed the action).

So, because there are many different objects in my application that need recording (Profiles, Companies, Pages, etc) I needed to create a table that would hold all the information for all the different objects.

I am using MSSQL but perhaps I should have looked at a document database but I am not sure whether using a SQL database for data and a document database for audits is a good idea....maybe someone has experience with this?

Anyway, my table looks like this:

CREATE TABLE [dbo].[cg_AuditTrail](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CompanyId] [uniqueidentifier] NOT NULL,
    [UserId] [uniqueidentifier] NOT NULL,
    [UserName] [nvarchar](255) NOT NULL,
    [ObjectName] [nvarchar](100) NOT NULL,
    [EventId] [int] NOT NULL,
    [Data] [xml] NOT NULL,
    [Date] [datetime] NOT NULL,
 CONSTRAINT [PK_cg_AuditTrail] PRIMARY KEY CLUSTERED 
(
    [Id] 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

ALTER TABLE [dbo].[cg_AuditTrail]  WITH CHECK ADD  CONSTRAINT [FK_cg_AuditTrail_cg_AuditEventTypes] FOREIGN KEY([EventId])
REFERENCES [dbo].[cg_AuditEventTypes] ([Id])
GO

ALTER TABLE [dbo].[cg_AuditTrail] CHECK CONSTRAINT [FK_cg_AuditTrail_cg_AuditEventTypes]
GO

As you can see I am storing the object as XML in the database. I have created a factory class in my project to convert any of my objects into serializable objects. For the most part this isn't necessary but for some objects it is. Here is an example one of my factory objects:

    public static Objects.Page PageFactory(Page Object)
    {
        Objects.Page FactoryObject = new Objects.Page()
        {
            Id = Object.Id,
            ParentId = Object.ParentId,
            UserId = Object.UserId,
            CompanyId = Object.CompanyId,
            Author = Object.Author,
            DateCreated = Object.DateCreated,
            DateModified = Object.DateModified,
            ModifiedById = Object.ModifiedById,
            ModifiedBy = Object.ModifiedBy,
            Name = Object.Name,
            Description = Object.Description,
            Path = Object.Path,
            FileName = Object.FileName,
            Link = Object.Link,
            ViewTitle = Object.ViewTitle,
            Restricted = Object.Restricted,
            Published = Object.Published,
            TypeId = Object.TypeId,
            Type = Object.Type.ToString(),
            Order = Object.Order,
        };

        return FactoryObject;
    }

So my questions are as follows:

1. Is this a good way to create the audit?


2. If I want to get some data our of the XML column, is that possible in SQL?


3. Would it be better using a document database instead of MSSQL?

Any help is appreciated, /r3plica

I am trying to make an Audit table that records pretty much everything in the system. I was going to use triggers, but I want to record things like searches, etc and I always want to record the Action-er (the user that performed the action).

So, because there are many different objects in my application that need recording (Profiles, Companies, Pages, etc) I needed to create a table that would hold all the information for all the different objects.

I am using MSSQL but perhaps I should have looked at a document database but I am not sure whether using a SQL database for data and a document database for audits is a good idea....maybe someone has experience with this?

Anyway, my table looks like this:

CREATE TABLE [dbo].[cg_AuditTrail](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CompanyId] [uniqueidentifier] NOT NULL,
    [UserId] [uniqueidentifier] NOT NULL,
    [UserName] [nvarchar](255) NOT NULL,
    [ObjectName] [nvarchar](100) NOT NULL,
    [EventId] [int] NOT NULL,
    [Data] [xml] NOT NULL,
    [Date] [datetime] NOT NULL,
 CONSTRAINT [PK_cg_AuditTrail] PRIMARY KEY CLUSTERED 
(
    [Id] 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

ALTER TABLE [dbo].[cg_AuditTrail]  WITH CHECK ADD  CONSTRAINT [FK_cg_AuditTrail_cg_AuditEventTypes] FOREIGN KEY([EventId])
REFERENCES [dbo].[cg_AuditEventTypes] ([Id])
GO

ALTER TABLE [dbo].[cg_AuditTrail] CHECK CONSTRAINT [FK_cg_AuditTrail_cg_AuditEventTypes]
GO

As you can see I am storing the object as XML in the database. I have created a factory class in my project to convert any of my objects into serializable objects. For the most part this isn't necessary but for some objects it is. Here is an example one of my factory objects:

    public static Objects.Page PageFactory(Page Object)
    {
        Objects.Page FactoryObject = new Objects.Page()
        {
            Id = Object.Id,
            ParentId = Object.ParentId,
            UserId = Object.UserId,
            CompanyId = Object.CompanyId,
            Author = Object.Author,
            DateCreated = Object.DateCreated,
            DateModified = Object.DateModified,
            ModifiedById = Object.ModifiedById,
            ModifiedBy = Object.ModifiedBy,
            Name = Object.Name,
            Description = Object.Description,
            Path = Object.Path,
            FileName = Object.FileName,
            Link = Object.Link,
            ViewTitle = Object.ViewTitle,
            Restricted = Object.Restricted,
            Published = Object.Published,
            TypeId = Object.TypeId,
            Type = Object.Type.ToString(),
            Order = Object.Order,
        };

        return FactoryObject;
    }

So my questions are as follows:

1. Is this a good way to create the audit?


2. If I want to get some data our of the XML column, is that possible in SQL?


3. Would it be better using a document database instead of MSSQL?

Any help is appreciated, /r3plica

Source