Capture Names of Updated Columns in Trigger  

Posted by ReelTym


CREATE TRIGGER tr_SalesHistory ON SalesHistory
FOR UPDATE
AS
BEGIN
DECLARE @FldsUpdated XML, @ColumnsUpdated VARBINARY(100)
SET @ColumnsUpdated = COLUMNS_UPDATED()
SET @FldsUpdated =
(
SELECT COLUMN_NAME AS Name
FROM TRS.INFORMATION_SCHEMA.COLUMNS Field
WHERE TABLE_NAME = 'SalesHistory'
AND sys.fn_IsBitSetInBitmask
(
@ColumnsUpdated,
COLUMNPROPERTY
(
OBJECT_ID( TABLE_SCHEMA + '.' + TABLE_NAME ),
COLUMN_NAME,
'ColumnID'
)
) <> 0
FOR XML AUTO, ROOT('Fields')
)

INSERT INTO SalesHistoryAudit
(
SaleID,
Product,
SaleDate,
SalePrice,
ColumnsUpdated
)
SELECT
SaleID,
Product,
SaleDate,
SalePrice,
@FldsUpdated
FROM INSERTED
END
GO

This entry was posted on Wednesday, June 17, 2009 and is filed under . You can leave a response and follow any responses to this entry through the Subscribe to: Post Comments (Atom) .

0 comments