Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Eran
Starting Member
6 Posts |
 Posted - 2011-09-22 : 09:13:28
|
| Hi guys,I'm using SqlServerExpress 2008 with .Net 2.0.Trying to use sqldependency for updatting the cache in my web application, only when it's really neccessary.This is my question:Some of the tables I store in cache, has columns that their change must not result the cache to be refreshed.For example, a column that is a counter that counts the number of times that a certain product was viewed. It's a column that is being changed very often, and I can live with that the statistics pages will not show a real time value.I will use an Absulute Expiration time of one hour or so, and then the cache will be fully refreshed anyway.When I update those columns, it is done by a spesific store procedure.Is it possibe (and if so, how?) to change the trigger (that was auto created by the aspnet_regsql command I run), so that it will do nothing is was raised after an update that was made by this spesific store procedure?This is the auto created trigger:USE [MyDb]GO/****** Object: Trigger [dbo].[MyTable_AspNet_SqlCacheNotification_Trigger] Script Date: 09/22/2011 16:09:24 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER TRIGGER [dbo].[MyTable_AspNet_SqlCacheNotification_Trigger] ON [dbo].[MyTable] FOR INSERT, UPDATE, DELETE AS BEGIN SET NOCOUNT ON EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure N'MyTable' END If it ain't possible, I guess I will have to move those columns to a different table, and I prefer avoiding that solution.Thanks,Eran |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-22 : 12:01:20
|
| you can simply add a if condition to check if that column was updated and then do required processingyou can make use of COLUMNS_UPDATED() function to check if what all columns are updatedhttp://msdn.microsoft.com/en-us/library/ms186329.aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-22 : 12:08:39
|
Where we have situations like that I make a 1:1 relationship between two tables, and one table has the fast changing data that I don't care about caching, or auditing, and the other (main) table has the core data that must be cached, be included in audit history, etc.There are probably easier ways of solving your current issue, so this is only food-for-thought  |
|
|
|
Eran
Starting Member
6 Posts |
Posted - 2011-09-23 : 05:54:42
|
| I thought it might be better to avoid mantaining the trigger logic whenever such a field will be added, but if it's not possible to know which SP did the update, than I'll use the UPDATE() function.1:1 table was my alternative solution, but I think I'll procceed with the UPDATE().Thank you both! :-)Eran |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-23 : 06:00:37
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-23 : 06:35:40
|
| Note that UPDATE() only tells you that the column was included in the UPDATE statement, not that it actually changed in one/many/all rows. For that you will need to compare INSERTED and DELETED tables |
|
|
|
|
|
|
|
|
sqldependency & Triggers