SQL Server记录上的CheckSum

Question

在SQL Server中的每条记录上添加校验和(仅由应用程序更新)，以防止和检查记录是否使用外部脚本手动更新，这是不是一个好主意？如果不是，最好的方法是什么？

Answer 1

使用触发器来防止更改(回滚它们)。或者使用它们来审核更改(将更改记录到一个单独的表中)。

-- Add trigger to prevent data changes 
CREATE TRIGGER [active].[myTriggerOfInfinitePower] ON [active].[mySpecialSparkleTable] 
FOR INSERT, UPDATE, DELETE AS 
BEGIN 

  -- Detect inserts 
  IF EXISTS (select * from inserted) AND NOT EXISTS (select * from deleted) 
    BEGIN 
        ROLLBACK TRANSACTION; 
        RAISERROR ('inserts are not allowed on that table', 15, 1); 
        RETURN; 
    END 

  -- Detect deletes 
  IF EXISTS (select * from deleted) AND NOT EXISTS (select * from inserted) 
    BEGIN 
        ROLLBACK TRANSACTION; 
        RAISERROR ('deletes are not allowed on that table', 15, 1); 
        RETURN; 
    END 

  -- Detect updates 
  IF EXISTS (select * from inserted) AND EXISTS (select * from deleted) 
    BEGIN 
        ROLLBACK TRANSACTION; 
        RAISERROR ('updates are not allowed on that table', 15, 1);  
        RETURN; 
    END 

END; 
GO 

来源：use triggers to prevent changes

关于校验和:添加校验和列(a)不会通过SQL脚本(等)“阻止”记录更改。以及(b)运行这样一个脚本的人总是可以更新校验和(理论上-除非你做一些秘密的松鼠散列)。