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