Testing DML Trigger in SQL Server

Hi,


DML Triggers
Testing for UPDATE or INSERT Actions to Specific Columns :
You can design a Transact-SQL trigger to perform certain actions based on UPDATE
 or INSERT modifications to specific columns. Use UPDATE() or COLUMNS_UPDATED in the body
 of the trigger for this purpose. UPDATE() tests for UPDATE or INSERT tries on one column. COLUMNS_UPDATED tests for UPDATE or INSERT actions
that are performed on multiple columns and returns a bit pattern that indicates which columns were inserted or updated.

Example>

A. Using an IF UPDATE() clause to test data modifications

CREATE TABLE my_table*
(a int NULL, b int NULL)
GO

CREATE TRIGGER my_trig
ON my_table
FOR INSERT
AS
IF UPDATE(b)
   PRINT 'Column b Modified'
GO

B. Using the COLUMNS UPDATED function to test data modifications

CREATE TRIGGER my_trig2
ON my_table
FOR INSERT
AS
IF ( COLUMNS_UPDATED() & 2 = 2 )
   PRINT 'Column b Modified'
GO

Please check below link for more information.
http://technet.microsoft.com/en-us/library/ms189799.aspx
http://msdn.microsoft.com/en-us/library/ms190227(v=sql.90).aspx
Hope this will help you.
Thanks.