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.