CODEWITHSUNDEEP
sqlsql-servertriggerssql-update
BDevAcc
BDevAcc

Reputation: 1

Stop Update if value trying to be updated is different SQL Server

I would like to stop an update query if the value of a column trying to be updated is different than what it is trying to be updated to. I have this trigger:

create trigger dbo.NoUpdate
on HistoricalValues With Encryption
for update
as
   if update (value)
   begin
       rollback transaction
   end

But I would like to add something like this (pseudo code):

if update(value) and oldValue != newValue
   rollback transaction

Where oldValue is the value currently in the row and new value is the proposed value from the update statement.

This has to work with SQL Server 2014 Express at a minimum.

Upvotes: 0

Views: 1058

Answers (1)

Thom A
Thom A

Reputation: 96015

To expand on my comment "If you don't want people to UPDATE a specific column, wouldn't you be better off with a permissions based approach?", personally I would do something like this:

CREATE TABLE dbo.YourTable (id int IDENTITY,
                            GoodColumn varchar(10),
                            BadColumn varchar(10));
GO

CREATE USER TestUser WITHOUT LOGIN;
GO

GRANT SELECT, UPDATE, DELETE, INSERT ON dbo.YourTable TO TestUser;
GO

DENY UPDATE ON dbo.YourTable(BadColumn) TO TestUser;
GO


INSERT INTO dbo.YourTable (GoodColumn,
                           BadColumn)
VALUES('test123','example12'),
      ('test456','example34');
GO

EXECUTE AS USER = 'TestUser';
GO
UPDATE dbo.YourTable
SET GoodColumn = 'Test789'
WHERE BadColumn = 'example34';
GO

UPDATE dbo.YourTable
SET BadColumn = 'Example56'
WHERE GoodColumn = 'test123';
GO

REVERT;
GO
SELECT *
FROM dbo.YourTable;

GO

DROP TABLE dbo.YourTable;
GO
DROP USER TestUser;

If, for some reason, you must use a TRIGGER, you could do something like this, however, I still suggest the permissions approach:

CREATE TRIGGER dbo.NoUpdateBad ON dbo.YourTable
AFTER UPDATE
AS

    IF EXISTS (SELECT 1
               FROM Inserted i
                    JOIN deleted d ON i.id = d.id
               WHERE d.BadColumn != i.BadColumn
                  OR (d.BadColumn IS NULL AND i.BadColumn IS NOT NULL)
                  OR (d.BadColumn IS NOT NULL AND i.BadColumn IS NULL))
        THROW 51000, N'Cannot change the value of the column "BadColumn".',1;

GO

UPDATE dbo.YourTable
SET BadColumn = 'asdjk'
WHERE id = 2;
GO

UPDATE dbo.YourTable
SET BadColumn = NULL
WHERE id = 1;

GO
SELECT *
FROM dbo.YourTable;

As I mention, however, neither of these will stop an sa account. DENY is ignored by sysadmin privileged accounts, and an sa would easily be able to disable to above trigger; even if you gave them a hurdle to jump first. it's much like thinking that turning xp_cmdshell well stop someone with access to an sa account. It doesn't, it's just a 1 second bump in the road to them, as they're just enable it.

Upvotes: 1

Related Questions