Log modifications

It is a best practice to log changes in important tables. This can be done in the Modifications table. To do this you should add a trigger on the tables you want to log. Recommended are [Jobs Scheduled], [Commands] and [Features].

First we create the function [GetUserName]

-- Create function [GetUserName]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF object_id('GetUserName', 'FN') IS NOT NULL
BEGIN
   DROP FUNCTION [dbo].[GetUserName]
END
GO

CREATE FUNCTION [dbo].[GetUserName]() RETURNS NVARCHAR(128)
AS
BEGIN
   RETURN substring(suser_sname(), charindex('\', suser_sname()) + 1, 128)
END

Example trigger [Commands]

– Add trigger to [Commands]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS ( SELECT 1
FROM sys.triggers
WHERE Name = ‘CommandsModifications’
)
BEGIN
DROP TRIGGER [dbo].[CommandsModifications]
END
GO

CREATE TRIGGER [dbo].[CommandsModifications]
ON [dbo].[Commands]
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON

  INSERT INTO [Modifications]
  	(	[Table]
  	,	[Date]
  	,	[Collector ID]
  	,	[Program]
  	,	[Action]
  	,	[Type]
  	,	[Data]
  	,	[SQL]
  	)
    SELECT	'Commands'
  	,	GETDATE()
  	,	dbo.GetUserName()
  	,	'Features'
  	,	'Update'
  	,	'Commands'
  	,	'Command ID: ' + CONVERT(NVARCHAR, INSERTED.[Command ID]) + ' Command: ' + INSERTED.[Command]
  	,	NULL 
  FROM	INSERTED
  JOIN	DELETED
  	ON	INSERTED.[Command ID] = DELETED.[Command ID]
  WHERE	INSERTED.[Command] <> DELETED.[Command]

  INSERT INTO [Modifications]
  	(	[Table]
  	,	[Date]
  	,	[Collector ID]
  	,	[Program]
  	,	[Action]
  	,	[Type]
  	,	[Data]
  	,	[SQL]
  	)
  SELECT 'Commands'
  	,	GETDATE()
  	,	dbo.GetUserName()
  	,	'Commands'
  	,	'Insert'
  	,	'Commands'
  	,	'Command ID: ' + CONVERT(NVARCHAR, INSERTED.[Command ID]) + ' Command: ' + INSERTED.[Command]
  	,	NULL 
  FROM INSERTED
  LEFT JOIN	DELETED
  		ON	INSERTED.[Command ID] = DELETED.[Command ID]
    WHERE	DELETED.[Command ID] is null

END

Example trigger [Features]

– Add trigger to [Features]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS ( SELECT 1
FROM sys.triggers
WHERE Name = ‘FeaturesModifications’
)
BEGIN
DROP TRIGGER [dbo].[FeaturesModifications]
END
GO

CREATE trigger [dbo].[FeaturesModifications] ON [dbo].[Features]
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON

  INSERT INTO [Modifications]
  	(	[Table]
  	,	[Date]
  	,	[Collector ID]
  	,	[Program]
  	,	[Action]
  	,	[Type]
  	,	[Data]
  	,	[SQL]
  	)
  SELECT	'Features'
  	,	GETDATE()
  	,	dbo.GetUserName()
  	,	'Features'
  	,	'Update'
  	,	'Features'
  	,	'Feature ID: ' + CONVERT(NVARCHAR, INSERTED.[Feature ID]) + ' Control name: ' + INSERTED.[Control name]
  	,	NULL 
  FROM	INSERTED
  JOIN	DELETED ON INSERTED.[Feature ID] = DELETED.[Feature ID]
  WHERE	INSERTED.[Data] <> DELETED.[Data]

  INSERT INTO [Modifications]
  	(	[Table]
  	,	[Date]
  	,	[Collector ID]
  	,	[Program]
  	,	[Action]
  	,	[Type]
  	,	[Data]
  	,	[SQL]
  	)
  SELECT	'Features'
  	,	GETDATE()
  	,	dbo.GetUserName()
  	,	'Features'
  	,	'Insert'
  	,	'Features'
  	,	'Feature ID: ' + CONVERT(NVARCHAR, INSERTED.[Feature ID]) + ' Control name: ' + INSERTED.[Control name]
  	,	NULL
  FROM	INSERTED
  INNER JOIN DELETED ON INSERTED.[Feature ID] = DELETED.[Feature ID]
  WHERE	DELETED.[Feature ID] is null

END

Example trigger [Jobs scheduled]

– Add trigger to [JobsScheduled]
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF EXISTS ( SELECT 1
FROM sys.triggers
WHERE Name = ‘JobsScheduledProgressModifications’
)
BEGIN
DROP TRIGGER [dbo].[JobsScheduledProgressModifications]
END
GO

CREATE TRIGGER [dbo].[JobsScheduledProgressModifications]
ON [dbo].[Jobs scheduled]
FOR INSERT, UPDATE
AS
BEGIN
– SET NOCOUNT ON added to prevent extra result sets from
– interfering with SELECT statements.
SET NOCOUNT ON;

  -- Insert statements for trigger here
  IF UPDATE([Progress])
  BEGIN
  	INSERT INTO [Modifications]
  		(	[Table]
  		,	[Date]
  		,	[Collector ID]
  		,	[Program]
  		,	[Action]
  		,	[Type]
  		,	[Data]
  		,	[SQL]
  		)
  	SELECT	'Jobs scheduled'
  		,	GETDATE()
  		,	dbo.GetUserName()
  		,	'Process_Jobs_Scheduled'
  		,	'Update job scheduled'
  		,	'Progress'
  		,	CONVERT(NVARCHAR(MAX), 'JOB: ') + [JOBS].[Description] +  ' - ' + CONVERT(NVARCHAR(3), [I].[Progress]) + '%  - Status: ' + [Jobs scheduled].[Status]
  		,	NULL 
  	FROM INSERTED I
  	INNER JOIN DELETED D on I.[ID] = D.[ID]
  	INNER JOIN [Jobs scheduled] ON [Jobs scheduled].[ID] = [I].[ID]
  	INNER JOIN [Jobs] ON [Jobs scheduled].[JOB ID] = [JOBS].[JOB ID]
		WHERE I.[Progress] <> d.[Progress]

  END

END

CAUTION: These modifications must be thoroughly tested before being put into service.