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.