Binaryworld - Checking for Changes within trigger ... [ SQL Server -> Trigger ]

The UPDATE and COLUMNS_UPDATED functions are available within both types of triggers to allow the trigger to determine which columns were modified by the triggering action statement. For example, the following trigger prevents any modifications to the lastname column in the Employees table. Here, the UPDATE function is used to determine if a modification was made to the column. If so, then an error is raised using the RAISERROR function and the transaction is rolled back, which undoes any changes that were made. The UPDATE function works in both the AFTER and INSTEAD OF triggers, but not outside of the triggers:

Click here to copy the following block

CREATE TRIGGER tr_Employees_U on Employees AFTER UPDATE AS
  IF UPDATE(lastname)
  BEGIN
    RAISERROR ('cannot change lastname', 16, 1)
    ROLLBACK TRAN
    RETURN
  END
GO

The UPDATE function is designed to determine if a single column was modified by an INSERT OR UPDATE statement. UPADATE (column) is the standard method used to check for updates. However, it becomes less efficient when the need arises to check if multiple columns were affected by an INSERT or UPDATE. This is where the COLUMNS_UPDATED function steps into the spotlight. The COLUMNS_UPDATED function returns a bitmask to evaluate if specific columns were modified. The bitmask contains a bit for every column in the table that was modified, in the order that the columns are defined in the table's schema. If the column was modified, the bit's value is 1; otherwise it is 0. Unlike the conventional way to read bytes going right to left, the bitmask reads from left to right. For example, the following code shows a trigger on the Order Details table that checks to see if both the Quantity and UnitPrice fields are modified:

Click here to copy the following block

CREATE TRIGGER tr_OrderDetails ON [Order Details] AFTER UPDATE
AS
  IF (COLUMNS_UPDATED() = 12)
  BEGIN
    RAISERROR ('Cannot change both UnitPrice and Quantity at the
          same time', 16, 1)
    ROLLBACK TRAN
  END
GO

If both fields are modified, an error is raised and the transaction is rolled back. For the Order Details table, the COLUMNS_UPDATED function returns a single byte with the first five bits representing the columns in the Order Details table. Since the situation called for determining if only the third and fourth columns were modified, it looked to see if only those bits were set to 1. When the third and fourth bits are turned on, it looks like this: 00110. Since this bitmask represents the powers of 2, the first bit represents 1, the second represents 2, the third represents 4, the fourth represents 8, and the fifth represents 16 (yes, this is the reverse order of bits in a normal binary number). Therefore, the bitmask value that means only the UnitPrice and Quantity were changed is 00110, which equates to the integer value of 12 (4 + 8). Note that this trigger only rolls back the transaction if the UnitPrice and Quantity fields were modified. If any other fields were modified, the bitmask would be different and thus not equal to the integer value of 12. If the trigger were modified to prohibit changes to these two fields even if other fields were modified, it could be rewritten like so:

Click here to copy the following block

ALTER TRIGGER tr_OrderDetails ON [Order Details] AFTER UPDATE
AS
  IF (COLUMNS_UPDATED() >= 12)
  BEGIN
    RAISERROR ('Cannot change both UnitPrice and Quantity
          at the same time', 16, 1)
    ROLLBACK TRAN
  END
GO

Notice how the COLUMNS_UPDATED function is now checked to see if the value is greater than or equal to 12. If you modified the UnitPrice, Quantity, and Discount columns, the bitmask would be 00111, resulting in an integer value of 28 (4 + 8 + 16). When there are more than eight columns in a table, this function returns the bitmasks in chunks of multiple bytes with the first eight columns in the first byte, columns 9 through 16 in the second byte, and so on. This function is more useful in determining which columns were updated than the UPDATE function for each column.
As demonstrated earlier, triggers can roll back transactions if a specific business rule is satisfied. When a trigger that contains a rollback is executed from a SQL batch the entire batch is canceled. Thus, all data that was modified by the triggering action is rolled back by the trigger's ROLLBACK TRANSACTION statement. However, a rollback does not cause the trigger to stop executing statements. Any statements following a ROLLBACK TRANSACTION statement are executed, which is fine, but there are consequences to putting statements after a ROLLBACK TRANSACTION. Specifically, as a trigger continues to execute any remaining statements after the rollback, any modifications that happen after the rollback are not rolled back. This occurs because once a ROLLBACK TRANSACTION has been issued within the trigger, the transaction is closed. Thus, when a new action query statement is executed, a new transaction begins separate from the original. Therefore, it is generally not recommended that you put any statements after a ROLLBACK TRANSACTION.
Just as a rollback does not automatically exit the trigger, it also does not raise an error automatically. If the rollback must occur and an error must be raised, the RAISERROR statement should follow the rollback immediately before exiting the trigger code.