SQL Server:阻止字段值更改一次！=NULL

Question

我想在SQL Server中创建一个触发器，如果某行中的某个特定字段已经包含NON-NULL值，则该触发器将阻止对该行进行更新。

然后它应该只是回滚更新。

背景：如果设置了onceChangedDate，它将无法更改或再次将其置空。

表格结构：

ID, UserName, Hidden, ChangedOneDate

每个条目在创建时通常具有：

ID, SomeUser, 0, NULL

我有一个触发器，它将把CCD_；隐藏的"设置为1。

然后我想防止将来对ChangedOnceDate进行任何更改。

我怎样才能做到这一点？

Answer 1

这有点像暗箭伤人，但看起来像是EXISTS，其中deleted伪表中的列的值不是NULL，而是在inserted伪表中

--Sample Table
CREATE TABLE dbo.YourTable (ID int IDENTITY(1,1),
SomeColumn varchar(10) NOT NULL,
NullableDate date NULL,
CONSTRAINT PK_YourTable PRIMARY KEY (ID));
GO
--Trigger solution
CREATE TRIGGER dbo.trg_NullableDateNulled_YourTable ON dbo.YourTable 
AFTER UPDATE AS
BEGIN
IF EXISTS (SELECT 1
FROM inserted i
JOIN deleted d ON i.ID = d.ID
WHERE d.NullableDate IS NOT NULL
AND i.NullableDate IS NULL)
--Use an Error number relevant for your environment
THROW 78921,
N'A row where the column ''NullableDate'' has been set to NULL has been detected in the trigger ''trg_NullableDateNulled_YourTable''. Cannot update column ''NullableDate'' to be NULL when it previously had a non-NULL value in the object ''dbo.YourTable''.',
16;
END;
GO

然后，您可以使用以下工具进行测试(并清理(：

INSERT INTO dbo.YourTable (SomeColumn, NullableDate)
VALUES('asda',NULL),
('wera',GETDATE());
GO
--Following fails
UPDATE dbo.YourTable
SET NullableDate = NULL
WHERE SomeColumn = 'wera';
GO
--Following works
UPDATE dbo.YourTable
SET NullableDate = GETDATE()
WHERE SomeColumn = 'asda';
GO
--Following works
UPDATE dbo.YourTable
SET NullableDate = '20220317'
WHERE SomeColumn = 'wera';
GO
--Following fails (as now not NULL
UPDATE dbo.YourTable
SET NullableDate = NULL
WHERE SomeColumn = 'asda';
GO
SELECT *
FROM dbo.YourTable;
GO
DROP TABLE dbo.YourTable;

db<gt；小提琴

Answer 2

从@Larnu的答案更改下面的部分

IF EXISTS (SELECT 1 FROM inserted i
JOIN deleted d ON i.ID = d.ID
WHERE d.NullableDate IS NOT NULL
AND i.NullableDate IS NULL)

至以下

IF EXISTS (SELECT 1 FROM deleted d WHERE d.NullableDate IS NOT NULL)

以获得您想要的确切行为。

dbfiddle