Blog ala Vidar

From SQL Server 2000, there’s been support for a function called COLUMNS_UPDATED. I haven’t used it much, but I’ve written triggers using it a couple of times, and every time I do that, I’ve forgot how it works. Now, I’m going to put it here on my blog

So, basically, this function returns which fields are actually updated. This can be useful if for instance you want users to be able to update one column, but not the others on a table. Instead of doing:

IF UPDATE(AllowedField) 
	AND NOT UPDATE(NotAllowedField1) 
	AND NOT UPDATE(NotAllowedField2) 
	AND NOT UPDATE(NotAllowedField3)
BEGIN
	RAISERROR('You are not allowed to change fields other than: AllowedField', 18, 1)
	ROLLBACK TRANSACTION
	RETURN
END

The problem with this solution is of course if you add new fields, you’re more or less proper-fucked.

CREATE TABLE test (
	NotAllowedField1 NVARCHAR(128),
	NotAllowedField2 NVARCHAR(128),
	NotAllowedField3 NVARCHAR(128),
	AllowedField NVARCHAR(128)
)
GO

INSERT INTO test
  (NotAllowedField1, NotAllowedField2, NotAllowedField3, AllowedField) 
VALUES
  ('aaaa', 'aaaa', 'aaaa', 'aaaa')
GO 5

CREATE TRIGGER test_ITrig ON test 
AFTER UPDATE 
AS
BEGIN
	DECLARE @AllowedFieldName AS NVARCHAR(128)
	DECLARE @ColumnNumber AS INT

	SET @AllowedFieldName = 'AllowedField'
	SET @ColumnNumber = (SELECT colid 
						FROM sys.syscolumns 
						WHERE id = OBJECT_ID('test') 
						  AND name = @AllowedFieldName)

	IF (COLUMNS_UPDATED() ^ POWER(2, ((@ColumnNumber) - 1))) > 0
	BEGIN
		RAISERROR('You are not allowed to change fields other than: %s', 
				18, 1, @AllowedFieldName)
		ROLLBACK TRANSACTION
		RETURN
	END
END
GO


-- Not allowed single
UPDATE test
	SET NotAllowedField1 = 'bbbb'

-- Not allowed multiple
UPDATE test
	SET
	  AllowedField = 'bbbb',
	  NotAllowedField2 = 'bbbb',
	  NotAllowedField3 = 'bbbb'

-- Allowed single
UPDATE test
	SET AllowedField = 'bbbb'


-- Clean-up
DROP TRIGGER test_ITrig
GO
DROP TABLE test
GO