I've got a SQL Server table which keeps track of customer name changes:
CREATE TABLE CustomerHistory
(
Id INT IDENTITY(1,1) PRIMARY KEY,
CustomerId INT NOT NULL,
Name VARCHAR(255) NOT NULL,
ValidFrom DATETIME NOT NULL,
ValidTo DATETIME NOT NULL,
CreatedOn DATETIME NOT NULL,
ModifiedOn DATETIME NOT NULL
)
INSERT INTO CustomerHistory (CustomerId, Name, ValidFrom, ValidTo, CreatedOn, ModifiedOn )
VALUES (1, 'ABC', '1900-01-01','2999-12-31', '2015-07-03 11:29:23.000', '2015-07-03 11:29:23.000')
Application allows user to make changes in two ways i.e. either by changing current record (Name and ModifiedOn are updated) or by inserting new record
INSERT INTO CustomerHistory (CustomerId, Name, ValidFrom, ValidTo, CreatedOn, ModifiedOn)
VALUES (1,'AAB', '2015-07-04','2999-12-31', '2015-07-04 12:29:23.000', '2015-07-04 12:29:23.000')
and updating previous one (Name, ValidTo, ModifiedOn are updated). Due to several unwanted changes done in the first way (updating current record only) I need to block it so each change made is done in the second way (inserting new record and updating previous one). I need to do it with trigger so users gets special error information.
Any idea how to cope with it?
You can use this trigger for example:
CREATE TRIGGER dbo.TR_InvalidateOldRows
ON CustomerHistory
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
-- Debug
--SELECT *
--FROM inserted
UPDATE ch
SET ValidTo = GETDATE(), ModifiedOn = GETDATE()
FROM (
SELECT TOP 1 Id, CustomerId, Name, ValidFrom, ValidTo, CreatedOn, ModifiedOn
FROM (
SELECT Id, CustomerId, Name, ValidFrom, ValidTo, CreatedOn, ModifiedOn
FROM CustomerHistory AS ch
WHERE ch.CustomerId = (SELECT CustomerId FROM inserted)
EXCEPT
SELECT Id, CustomerId, Name, ValidFrom, ValidTo, CreatedOn, ModifiedOn
FROM inserted
) as allRows
ORDER BY ID DESC
) as oldRow
INNER JOIN CustomerHistory as ch
ON oldRow.id = ch.Id
END
GO
Filled with demo data using:
INSERT INTO CustomerHistory (CustomerId, Name, ValidFrom, ValidTo, CreatedOn, ModifiedOn)
VALUES (1,'AAB', GETDATE(),DATEADD(year,1,GETDATE()), GETDATE(), GETDATE())
The table is filled with:
Id CustomerId Name ValidFrom ValidTo CreatedOn ModifiedOn
----------- ----------- ---------- ----------------------- ----------------------- ----------------------- -----------------------
1 1 AAB 2015-07-04 13:21:34.500 2016-07-04 13:21:34.500 2015-07-04 13:21:34.500 2015-07-04 13:21:34.500
If you run the Insert again it will result in this:
Id CustomerId Name ValidFrom ValidTo CreatedOn ModifiedOn
----------- ----------- ---------- ----------------------- ----------------------- ----------------------- -----------------------
1 1 AAB 2015-07-04 13:21:34.500 2015-07-04 13:22:02.163 2015-07-04 13:21:34.500 2015-07-04 13:22:02.163
2 1 AAB 2015-07-04 13:22:02.153 2016-07-04 13:22:02.153 2015-07-04 13:22:02.153 2015-07-04 13:22:02.153
This trigger will just invalidate all older rows of a specific CustomerId.
If you want additionally to set the ModifiedOn date if an update occurs you can create this additional trigger:
CREATE TRIGGER dbo.TR_UpdateModifiedOn
ON CustomerHistory
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
-- Debug
--SELECT *
--FROM inserted
UPDATE ch
SET ModifiedOn = GETDATE()
FROM inserted as i
INNER JOIN CustomerHistory as ch
ON i.id = ch.Id
END
GO
By the way, a procedure which handles this would be better than a trigger. But in some cases you can't provide a procedure. For example if users will manipulate the data using SSMS, Access or direct updates through any other applications.
Edit based on the Feedback
I leave the old code above just in case someone else needed a solution like described above. In your case you just want to block all Updates except they update only the ModifiedOn and the ValidTo columns.
In this case this trigger will solve it:
CREATE TRIGGER dbo.TR_InsteadUpdate
ON CustomerHistory
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON;
-- Add your conditions here
IF (
-- e.g. Updated a row without modifieng the ModifiedOn Column
SELECT COUNT(*)
FROM inserted as i
INNER JOIN deleted as d
ON i.Id = d.Id
AND i.CustomerId = d.CustomerId
AND i.Name = d.Name
AND i.ValidFrom = d.ValidFrom
AND i.CreatedOn = d.CreatedOn
AND i.ValidTo <> d.ValidTo
AND i.ModifiedOn <> d.ModifiedOn
) = 0 BEGIN
-- Not allowed, rollback
RAISERROR(N'Not allowed!',16,1) WITH NOWAIT
ROLLBACK TRANSACTION
END
-- Otherwise update it
UPDATE ch
SET ModifiedOn = i.ModifiedOn, ValidTo = i.ValidTo
FROM CustomerHistory AS ch
INNER JOIN inserted AS i
ON i.Id = ch.Id
END
GO
This code will throw an exception:
-- not allowed
UPDATE CustomerHistory
SET Name = N'EEE'
WHERE id = 2
While this one will work:
-- allowed
UPDATE CustomerHistory
SET ModifiedOn = GETDATE(),
ValidTo = DATEADD(day,1,GETDATE())
WHERE id = 2
Another addition can be an WHERE-clause inside the INSTEAD OF-Trigger inside the IF-clause. Which will prevent updates on the two mentioned columns, if the Insert is older than 60 seconds (for example). This can be achieved by adding this row to the IF:
WHERE DATEDIFF(SECOND,d.CreatedOn,i.ModifiedOn) < 60
Which results into this code:
CREATE TRIGGER dbo.TR_InsteadUpdate
ON CustomerHistory
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON;
-- Add your conditions here
IF (
-- e.g. Updated a row without modifieng the ModifiedOn Column
SELECT COUNT(*)
FROM inserted as i
INNER JOIN deleted as d
ON i.Id = d.Id
AND i.CustomerId = d.CustomerId
AND i.Name = d.Name
AND i.ValidFrom = d.ValidFrom
AND i.CreatedOn = d.CreatedOn
AND i.ValidTo <> d.ValidTo
AND i.ModifiedOn <> d.ModifiedOn
WHERE DATEDIFF(SECOND,d.CreatedOn,i.ModifiedOn) < 60
) = 0 BEGIN
-- Not allowed, rollback
RAISERROR(N'Not allowed!',16,1) WITH NOWAIT
ROLLBACK TRANSACTION
END
-- Otherwise update it
UPDATE ch
SET ModifiedOn = i.ModifiedOn, ValidTo = i.ValidTo
FROM CustomerHistory AS ch
INNER JOIN inserted AS i
ON i.Id = ch.Id
END
GO
Addition for really broad tables
If you have a really broad table with many columns and you won't to maintain all columns and just want to add the allowed columns you can probably use the following trigger which uses a hash to compare the old and the new row instead. It will work after the same principle of the above one but uses a dynamic hash algorithm.
CREATE TRIGGER dbo.TR_InsteadUpdate
ON CustomerHistory
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql nvarchar(max), @hash_before varbinary(max),
@hash_after varbinary(max), @columnlist nvarchar(max),
@paramDefinition nvarchar(500) = N'@hash_value varbinary(max) OUTPUT';
SELECT @columnlist = COALESCE(
@columnlist + N'+ISNULL(CONVERT(nvarchar(max),'+ COLUMN_NAME + N'),N'''')',
N'ISNULL(CONVERT(nvarchar(max),'+ COLUMN_NAME + N'),N'''')')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'CustomerHistory'
-- Which columns are allowed to be updated?
AND COLUMN_NAME NOT IN(N'ModifiedOn',N'ValidTo')
-- needed due to scope of deleted and inserted
SELECT * INTO #deleted FROM deleted
-- Get the hash-value for the before-values
SET @sql = N'
SELECT @hash_value = HASHBYTES(''SHA1'','+@columnlist+')
FROM #deleted'
EXECUTE sp_executesql @sql, @paramDefinition, @hash_value = @hash_before OUTPUT;
DROP TABLE #deleted
SELECT * INTO #inserted FROM inserted
-- Get the hash-value for the after-values
SET @sql = N'
SELECT @hash_value = HASHBYTES(''SHA1'','+@columnlist+')
FROM #inserted'
EXECUTE sp_executesql @sql, @paramDefinition, @hash_value = @hash_after OUTPUT;
DROP TABLE #inserted
SELECT @hash_before, @hash_after
IF (@hash_before <> @hash_after) BEGIN
-- Not allowed, rollback
RAISERROR(N'Not allowed!',16,1) WITH NOWAIT
ROLLBACK TRANSACTION
END
-- Otherwise update it
UPDATE ch
SET ModifiedOn = i.ModifiedOn, ValidTo = i.ValidTo
FROM CustomerHistory AS ch
INNER JOIN inserted AS i
ON i.Id = ch.Id
END
GO
