Trigger to block some updates and allow inserts

Question

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?

Answer 1

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

Answer 2

Idealy I would recommend allowing users only to execute a stored procedure that will encapsulate the logic.
If you can't use a stored procedure, You can use an instead of update trigger on the table. That way the only way to update existing records is going through this trigger.