SQL Server prevent all records from being updated or deleted

Question

I am running SQL Server 2008. I written code that should be pretty safe to prevent any records being deleted or updated, but would be much happier if I could do this at the database level. Is it possible to mark a table so that once a row has been inserted it can never be modified or deleted?

Answer 1

Edit per comments. It seems you are actually looking for Versioning which really shouldn't be done via triggers but it can be with a performance impact and a Lot more coding.

Most appropriate method to combat your concern. Maintain transactional backups every X# of minutes so you can roll back if it gets messed up.

However sql-server does have 2 change tracking methods built in that you could explore.

• Change Tracking - which simply identifies if a record was modified, and is really useful when synchronizing changes to a DB. Basically it increments a BIGINT for every row for every operation so you just have to check for records greater than the previous synchronize number.
• Change Data Capture - this will capture the insert/update/delete and the state of the record (row).

For your particular worry Change Data Capture might be plausible and way more easy to maintain than triggers. I haven't tried it myself because Change Tracking was enough for my needs but here is a link to Microsoft's Documentation https://msdn.microsoft.com/en-us/library/cc645937(v=sql.110).aspx

If you insist on Triggers here would be an example, you will have to maintain the original primary key for referential integrity or you might not know which change caused the problem

CREATE TABLE TblName (
    PrimaryKeyID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
    ,Col1 INT NULL
    ,Col2 INT NULL
    ,OriginalPrimaryKeyId INT NULL
    ,CreateDate DATETIME DEFAULT(GETDATE())
    ,UpdateDate DATETIME DEFAULT(GETDATE())
    ,IsLatestVersion BIT NOT NULL DEFAULT(1)
)
GO

CREATE TRIGGER dbo.TrigForInsertEnforceVersionColTblName ON dbo.TblName
FOR INSERT
AS
BEGIN
BEGIN TRY
    IF (SELECT COUNT(*) FROM TblName WHERE IsLatestVersion <> 1 AND OriginalPrimaryKeyId IS NULL) > 0
    BEGIN
       ;THROW 51000, 'Attempted to insert a record identified as Previous Version without referencing another record', 1
    END

END TRY
BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
    --this will mean the loss of some Primary Keys but it is better than an
    --INSTEAD of INSERT because you wont have to handle the insert code

    ;THROW
END CATCH

END
GO

CREATE TRIGGER dbo.TriggerName ON dbo.TblName
INSTEAD OF UPDATE, DELETE

AS 
BEGIN

BEGIN TRY

    IF EXISTS (
       SELECT *
       FROM
          TblName t
          INNER JOIN inserted i
          ON t.PrimaryKeyID = i.PrimaryKeyID
          AND (t.OriginalPrimaryKeyId <> i.OriginalPrimaryKeyId
             OR t.IsLatestVersion <> i.IsLatestVersion)
    )
    BEGIN
       ;THROW 51000, 'OriginalPrimaryKeyId Column or IsLatestVersion Column was attempted to be updated', 1
    END

    --don't have to test count can just run the update statement
    IF ((SELECT COUNT(*) FROM inserted) > 0)
    BEGIN
       --It's an UPDATE Operations so insert new row but maintain original primary key
       --so you know what the new row is a version of
       INSERT INTO dbo.TblName (Col1, Col2, OriginalPrimaryKeyId)
       SELECT
          i.Col1
          ,i.Col2
          ,OriginalPrimaryKeyId = CASE
                WHEN t.OriginalPrimaryKeyId IS NULL THEN t.PrimaryKeyID
                ELSE t.OriginalPrimaryKeyId
          END
       FROM
          inserted i
          INNER JOIN TblName t
          ON i.PrimaryKeyID = t.PrimaryKeyID
    END

    UPDATE t
    SET IsLatestVersion = 0
        ,UpdateDate = GETDATE()
    FROM
       TblName t
       INNER JOIN deleted d
       ON t.PrimaryKeyID = d.PrimaryKeyID

END TRY
BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION

    ;THROW
END CATCH

END

Permissions discussion:

For anything row level and to block everyone including database owner role or administrators you would have to create a trigger. But those roles could always remove the trigger too and modify the table. Perhaps simple Permissions would be enough such as

on an entire role, which would be best if you put the users in a role

GRANT INSERT ON SchemaName.TableName TO RoleName
DENY UPDATE ON SchemaName.TableName TO RoleName
DENY DELETE ON SchemaName.TableName TO RoleName

OR for specific users same commands just change RoleName to username

DENY UPDATE ON SchemaName.TableName TO UserName

This would grant the ability to insert but revoke ability to update or delete a record.

You can also deny execute, alter, and a bunch more here is Microsoft's documentation: https://msdn.microsoft.com/en-us/library/ms173724.aspx

Using a trigger instead of security permissions is a lot messier and if someone with enough permissions wants to make a change they still can it will just slow them down but not by much. So if you are worried about that ability make sure you have good backups.