Dynamic trigger logging

• Peter Kruis

  Mr or Mrs. 500

  Points: 577

  More actions

  October 13, 2015 at 8:06 am

  #308794

  I would like to have a dynamic trigger on INSERT, UPDATE and DELETE. (each their own trigger).

  What i want, is the trigger to log what has happened with the record. For the insert trigger, i would like to select all columns and put them in 1 column to my logging table. For the update trigger, i need the same, however for both of the old and new items.

  For example:

  CREATE TABLE [dbo].[TRIGGER_TEST](

  [COLUMNA] [int] IDENTITY(1,1) NOT NULL,

  [COLUMNB] [nvarchar](50) NOT NULL,

  [COLUMNC] [nvarchar](20) NULL,

  [COLUMND] [date] NULL,

  [COLUME] [decimal](2, 2) NULL,

  CONSTRAINT [PK_TRIGGER_TEST] PRIMARY KEY CLUSTERED

  (

  [COLUMNA] ASC

  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

  ) ON [PRIMARY]

  /****** Object: Trigger [dbo].[testTrigger] Script Date: 13-10-2015 15:58:56 ******/

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  -- =============================================

  -- Author:<Author,,Name>

  -- Create date: <Create Date,,>

  -- Description:<Description,,>

  -- =============================================

  CREATE TRIGGER [dbo].[testTrigger]

  ON [dbo].[TRIGGER_TEST]

  AFTER INSERT

  AS

  BEGIN

  -- SET NOCOUNT ON added to prevent extra result sets from

  -- interfering with SELECT statements.

  SET NOCOUNT ON;

  -- Insert statements for trigger here

  SELECT *

  FROM INSERTED

  --insert all the items (dynamically) in a new table for logging purposes.

  END

  GO

  CREATE TABLE [dbo].[TRIGGER_LOGGING](

  [ID] [int] IDENTITY(1,1) NOT NULL,

  [OLD] [nvarchar](max) NULL,

  [NEW] [nvarchar](max) NULL,

  [PK] [int] NULL,

  CONSTRAINT [PK_TRIGGER_LOGGING] 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] TEXTIMAGE_ON [PRIMARY]

  GO

  When I insert an item in TRIGGER_TEST, i would like to see what is inserted in the TRIGGER_LOGGING table.

  INSERT INTO [dbo].[TRIGGER_TEST]

  ([COLUMNB]

  ,[COLUMNC]

  ,[COLUMND]

  ,[COLUME])

  VALUES

  ('test'

  ,'hello'

  ,getdate()

  ,0.1)

  GO

  I would like to see in my TRIGGER_LOGGING table in NEW: COLUMNB='test'|COLUMNC='hello'|COLUMND='2015-10-13 16:04'|COLUME=0,10

  (or something like this 😉 )