SQL Server : prevent change of field value when it was once != NULL-CodePudding

I want to create a trigger in SQL Server that prevents an update on a row if one specific field in that row already contains NON-NULL values.

It should then just ROLLBACK the update.

Background: if a onceChangedDate is set, it shall not be able to change it or NULL it again.

Table structure:

ID, UserName, Hidden, ChangedOneDate

Each entry will have normally at creation:

ID, SomeUser, 0, NULL

I have a trigger which will set ChangedOnceDate to the current date as soon the "Hidden" is set to 1.

And then I want to prevent any change on ChangedOnceDate for future.

How can I achieve this?

CodePudding user response：

This is some what of a stab in the dark, but seems like an EXISTS where the value of the column in the deleted pseudo-table isn't NULL but is in the inserted pseudo-table is what you are after:

--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

You can then test (and clean up) with the following:

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<>fiddle