Sql Table Log

CREATE LOG TABLE

USE [DATABASE]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Lg_Audit](
[LogId] [bigint] IDENTITY(1,1) NOT NULL,
[TABLENAME] [nvarchar](100) NULL,
[PK] [nvarchar](100) NULL,
[LMDATE] [datetime] NULL,
[OLDVALUE] [xml] NULL,
[NEWVALUE] [xml] NULL,
 CONSTRAINT [PK_Lg_Audit] 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] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Lg_Audit] ADD  CONSTRAINT [DF_Lg_Audit_LMDATE]  DEFAULT (getdate()) FOR [LMDATE]
GO





CREATE TIGGRT FOR TABLE INSERTION LOG

USE [DATABASE]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[TI_AUDIT_TABLENAME_INS]
    ON [dbo].[TABLENAME]
    AFTER INSERT

AS BEGIN

    SET NOCOUNT ON
    SET XACT_ABORT ON

    DECLARE @PK bigint

    DECLARE cur CURSOR FORWARD_ONLY READ_ONLY LOCAL FOR
        SELECT PK FROM INSERTED

    OPEN cur

    FETCH NEXT FROM cur INTO @PK

    WHILE @@FETCH_STATUS = 0 BEGIN

        DECLARE
              @BeforeChange XML
            , @AfterChange XML

        SELECT @BeforeChange = (
            SELECT *
            FROM DELETED
            WHERE PK = @PK
            FOR XML RAW, ROOT
        )
        , @AfterChange = (
            SELECT *
            FROM INSERTED
            WHERE PK = @PK
            FOR XML RAW, ROOT
        )

INSERT INTO dbo.Lg_Audit (TABLENAME,PK,OLDVALUE,NEWVALUE)
SELECT 'TABLENAME','PK', @BeforeChange, @AfterChange


        FETCH NEXT FROM cur INTO @PK

    END

    CLOSE cur
    DEALLOCATE cur

END
GO

ALTER TABLE [dbo].[TABLENAME] ENABLE TRIGGER [TI_AUDIT_TABLENAME_INS]
GO




CREATE TIGGRT FOR TABLE UPDATE LOG

USE [DATABASE]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[TI_AUDIT_TABLENAME_UP]
    ON [dbo].[TABLENAME]
    AFTER UPDATE

AS BEGIN

    SET NOCOUNT ON
    SET XACT_ABORT ON

    DECLARE @PK bigint

    DECLARE cur CURSOR FORWARD_ONLY READ_ONLY LOCAL FOR
        SELECT PK FROM INSERTED

    OPEN cur

    FETCH NEXT FROM cur INTO @PK

    WHILE @@FETCH_STATUS = 0 BEGIN

        DECLARE
              @BeforeChange XML
            , @AfterChange XML

        SELECT @BeforeChange = (
            SELECT *
            FROM DELETED
            WHERE PK = @PK
            FOR XML RAW, ROOT
        )
        , @AfterChange = (
            SELECT *
            FROM INSERTED
            WHERE PK = @PK
            FOR XML RAW, ROOT
        )

INSERT INTO dbo.Lg_Audit (TABLENAME,PK,OLDVALUE,NEWVALUE)
SELECT 'TABLENAME','PK', @BeforeChange, @AfterChange

        FETCH NEXT FROM cur INTO @PK

    END

    CLOSE cur
    DEALLOCATE cur

END
GO

ALTER TABLE [dbo].[TABLENAME] ENABLE TRIGGER [TI_AUDIT_TABLENAME_UP]
GO