Sunday, February 19, 2012

Commiting a trigger under certain conditions

I have a sitemapcache that caches nodes with a sqlcachedependency. Everything is working fine but one thing. Every time you visit a forum, the viewcount is changed, therefore raising the trigger and dropping my cache object. How do i make it so that the trigger is only fired if I update the Title or Description field?

ALTER TRIGGER [dbo].[sp_Forums_Topics_AspNet_SqlCacheNotification_Trigger]ON [dbo].[sp_Forums_Topics]
FOR INSERT,UPDATE,DELETE AS BEGIN
SET NOCOUNT ON
EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure N'sp_Forums_Topics'END

Hi,

You can use UPDATE() clause of transact sql to do that. I think it is only specific to 2005, but you could check it in 2000 too. Example is given below.

USE AdventureWorks;GOIFEXISTS (SELECT name FROM sys.objectsWHERE name ='reminder'AND type ='TR')DROP TRIGGER Person.reminder;GOCREATE TRIGGER reminderON Person.AddressAFTERUPDATE AS IF (UPDATE (StateProvinceID)ORUPDATE (PostalCode) )BEGINRAISERROR (50009, 16, 10)END;GO-- Test the trigger.UPDATE Person.AddressSET PostalCode = 99999WHERE PostalCode ='12345';GO

No comments:

Post a Comment