Microsoft® SQL Server® 2012 Bible

888

Part VII: Monitoring and Auditing

CREATE FUNCTION dbo.GenColUpdated

(@Col INT, @ColTotal INT)

RETURNS INT

AS

BEGIN;

-- Copyright 2001 Paul Nielsen

-- This function simulates the Columns_Updated() behavior

DECLARE

@ColByte INT,

@ColTotalByte INT,

@ColBit INT;

-- Calculate Byte Positions

SET @ColTotalByte =  1 + ((@ColTotal-1) /8);

SET @ColByte = 1 + ((@Col-1)/8);

SET @ColBit = @Col - ((@ColByte-1) * 8);

RETURN Power(2, @ColBit + ((@ColTotalByte-@ColByte) * 8)-1);

END;

To use this function, perform a bitwise AND (&) between columns_updated() and

GenColUpdated(). If the bitwise and is equal to GenColUpdated(), then the column in

question is indeed updated:

...
If COLUMNS_UPDATED()& dbo.GenColUpdated(@ColCounter,@ColTotal) = 
@ColUpdatedTemp

Inserted and Deleted Logical Tables

SQL Server provides the inserted and deleted logical tables as read-only images of the

data affected by the DML statement. The deleted table contains the version of the rows

before and the inserted table version after the effects of the DML statement, as shown in

Table 36-2.

TABLE 36 -2 Inserted and Deleted Tables

DML Statement Inserted Table Deleted Table

Insert Rows being inserted Empty

Update Row versions after update Rows versions before update

Delete Empty Rows being deleted

c36.indd 888c36.indd 888 7/31/2012 10:03:08 AM7/31/2012 10:03:08 AM

http://www.it-ebooks.info