Recently I was start to complete my Understanding the Basic of Triggers Article Part II hope to include all topics related Triggers in that case I want to explain COLUMNS_UPDATED (Transact-SQL) Syntax because everybody know it`s very useful when we working with triggers . Hope everybody familiar with COLUMNS_UPDATED (Transact-SQL) Syntax so I’m not going to explain it this time (after complete my Article then you can get clear idea so pls wait for while). I want to say there is small mistake in SQL Server 2008 Books Online (November 2009) under COLUMNS_UPDATED (Transact-SQL) category, example of “B. Using COLUMNS_UPDATED to test more than eight columns” under this headline. I’m honestly saying this is not big mistake but my view is Microsoft Technical writing and QA team needs to get responsibility of this mistake. So when we comes to this mistake,
IF OBJECT_ID (N'uContact2', N'TR') IS NOT NULL
DROP TRIGGER Person.tr1; <---mistake
CREATE TRIGGER uContact2 ON Person.Contact
AFTER UPDATE AS
IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1) & 20 = 20)
AND (SUBSTRING(COLUMNS_UPDATED(),2,1) & 1 = 1) )
PRINT 'Columns 3, 5 and 9 updated';
SET Title=Title, MiddleName=MiddleName,
If you run above SQL statement in first time your AdventureWorks database its runs successfully but if you run it again then it will gives you following error.
Msg 2714, Level 16, State 2, Procedure uContact2, Line 5 There is already an object named 'uContact2' in the database.
Hope now everybody will understand the mistake and it’s not big mistake either it’s not good for company like Microsoft.