CODEWITHSUNDEEP
sqlsql-servert-sql
Maddy
Maddy

Reputation: 3816

How do I efficiently prevent updates to all but one column in a trigger?

I have a table with 250 columns, on which I have to write a trigger which will prevent updates to all but one column.

There is a column evt_updatable which can be updatable. The responsibility of trigger is to raise an error when user trys to update anything other than this column.

what are the different ways to achieve this. one strait way is to declare 500 variables which stores inserted and deleted values. as shown in below code snippet.

SELECT         @newColdata1              = i.col1
               @oldColdata1              = d.col1
               @newColdata2              = i.col2
               @oldColdata2              = d.col1
                  ----------------------------
                ------------------------------ -- same thing for remaining 250 rows
     FROM      inserted i, deleted d
     WHERE     i.evt_code = d.evt_code;

Than comparing them all for changes and raise an error if something is changed. Is there any other better way of doing this. as it is taking 500 different variable declarations and many more comparisons. please help me in this regard

Simply : In my table user should able to change only one specific column if he tries to change any thing else trigger should raise an error.

Upvotes: 2

Views: 980

Answers (1)

Stephen Turner
Stephen Turner

Reputation: 7314

This is a good use for the COLUMNS_UPDATED() function that is available in a trigger.

If only the fourth column has been updated then the bit pattern it returns will be something like 

00001000 00000000 00000000 00000000 ... (binary)

i.e. the first byte has the the value 8 and the rest are zero, if any other column is updated then either the first byte will be not equal to 8, or one of the others won't be 0.

The function returns a varbinary, one bit for each column. For 250 columns it will return 32 bytes of data (250/8) this means you will need to test each byte i.e.

IF (    (SUBSTRING(COLUMNS_UPDATED(), 1,1) = 8) 
    AND (SUBSTRING(COLUMNS_UPDATED(), 2,1) = 0) 
    AND (SUBSTRING(COLUMNS_UPDATED(), 3,1) = 0) 
    .
    .
    .
    AND (SUBSTRING(COLUMNS_UPDATED(),31,1) = 0) 
    AND (SUBSTRING(COLUMNS_UPDATED(),32,1) = 0) 
)

This tests that exactly one specific column has been updated. If you have multiple columns that could be updated then you will need some bitwise operators to filter the bit pattern.

Upvotes: 3

Related Questions