Database Reference
In-Depth Information
Listing 8-15. UPDATE() function behavior
create trigger trg_T_AU
on dbo.T
after update
as
begin
-- Some code here
if update(C)
-- Some code here
end
go
declare @V int = null
update T set C = IsNull(@V, C) where ID = 1;
Listing 8-16 shows an example of the trigger that recalculates the order total when a line item price or
quantity changes.
Listing 8-16. UPDATE() function implementation example
create trigger trg_OrderLineItems_AfterUpdate
on dbo.OrderLineItems
after update
as
begin
-- Some code here
if update(Quantity) or update(Price)
begin
-- recalculating order total
update o
set
o.Total =
(
select sum(li.Price * li.Quantity)
from dbo.OrderLineItems li
where li.OrderId = o.OrderId
)
from dbo.Orders o
where o.OrderId in (select OrderId from inserted)
end
-- Some code here
end
The COLUMNS_UPDATED function returns the varbinary value that represents the bitmask where each bit is set to 1
in case the column was affected by the statement. The order of the bits, from least significant to the most significant,
corresponds to column_id value from the sys.columns catalog view.
Assuming that the column_id for the Quantity column is 4 and the column_id for the Price column is 5, we can
replace the if operator above with the following bitmask comparison: if columns_updated() & 24 <> 0 .
The integer value 24 represents the binary value 11000. The result of bitwise & (and) operator would be not equal
to zero if either of the corresponding bits returned by the columns_updated function is set to one.
 
Search WWH ::




Custom Search