Code download available at:DataPoints0401.exe(111 KB)
Figure 1 View Joining Customers and Their Order Details
CREATE VIEW vwCustomersOrdersOrderDetailsProducts AS SELECT c.CustomerID, c.CompanyName, o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity, od.Discount, p.ProductID, p.ProductName FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID INNER JOIN [Order Details] od ON o.OrderID = od.OrderID INNER JOIN Products p ON od.ProductID = p.ProductID GO
Figure 2 INSTEAD OF Trigger Updating a View
CREATE TRIGGER tr_vwCustomersOrdersOrderDetailsProducts_IO_U ON vwCustomersOrdersOrderDetailsProducts INSTEAD OF UPDATE AS — Update the Customers UPDATE Customers SET CompanyName = i.CompanyName FROM inserted i INNER JOIN Customers c ON i.CustomerID = c.CustomerID — Update the Orders UPDATE Orders SET OrderDate = i.OrderDate FROM inserted i INNER JOIN Orders o ON i.OrderID = o.OrderID — Update the Order Details UPDATE [Order Details] SET UnitPrice = i.UnitPrice, Quantity = i.Quantity FROM inserted i INNER JOIN [Order Details] od ON i.OrderID = od.OrderID AND i.ProductID = od.ProductID — Update the Products UPDATE Products SET ProductName = i.ProductName FROM inserted i INNER JOIN Products p ON i.ProductID = p.ProductID GO
UPDATE vwCustomersOrdersOrderDetailsProducts SET Quantity = 100, UnitPrice = 20, CompanyName = 'Fake Name', OrderDate = '11/23/2001', ProductName = 'Widget' WHERE OrderID = 10265 AND ProductID = 17
CREATE VIEW vwOrdersOrderDetailsProducts AS SELECT o.OrderID, o.OrderDate, od.UnitPrice * od.Quantity AS ExtendedPrice, p.ProductID, p.ProductName FROM Orders o INNER JOIN [Order Details] od ON o.OrderID = od.OrderID INNER JOIN Products p ON od.ProductID = p.ProductID GO
CREATE TRIGGER tr_vwOrdersOrderDetailsProducts_IO_U ON vwOrdersOrderDetailsProducts INSTEAD OF UPDATE AS UPDATE [Order Details] SET UnitPrice = i.ExtendedPrice / Quantity FROM inserted i INNER JOIN [Order Details] od ON i.OrderID = od.OrderID AND i.ProductID = od.ProductID GO
UPDATE vwOrdersOrderDetailsProducts SET ExtendedPrice = 200 WHERE OrderID = 10265 AND ProductID = 17
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
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:
ALTER TRIGGER tr_OrderDetails ON [Order Details] AFTER UPDATE AS IF (COLUMNS_UPDATED() & 12 >= 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.
[Editor's Update - 12/6/2004: This trigger incorrectly compared the sum of the bit mask to the sum of the 3rd and 4th columns using the condition "COLUMNS_UPDATED() >= 12". Any time a column after the 4th column was updated, this trigger would incorrectly raise an error. The code for the trigger has been corrected to take this into consideration by using the binary & operator.] 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.
Send your questions and comments for John to email@example.com.
More MSDN Magazine Blog entries >
Browse All MSDN Magazines
Subscribe to MSDN Flash newsletter
Receive the MSDN Flash e-mail newsletter every other week, with news and information personalized to your interests and areas of focus.