none
sqldependency & Triggers RRS feed

  • Question

  • Hi guys,

    I'm using SqlServerExpress 2008 R2 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 ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER 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

    Thursday, September 22, 2011 1:28 PM

Answers

All replies

  • Hello,

    After I checked your question, would you like to refresh sql cache with a granularity time?

    If I misunderstood, please feel free to let us know.

    Thanks,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, September 26, 2011 6:04 AM
  • Hi,

    I would like to referesh the cache only when something was actually was changed,

    and only when this change is a change that really requires a referesh.

    What I already did meanwhile, is using the UPDATE() function at the trigger,

    to check if a specific field was changes, and decide according to that if I should call dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure  or not.

     

    Thanks,

    Eran

     


    Eran.
    Wednesday, September 28, 2011 9:13 PM
  • Hello,

    ASP.NET queries table repeatedly keeps track of the most recent changed values for every table. When this value changes in a subsequent read, ASP.NET knows that the table has changed. Regarding ASP.NET cache, I suggest you can check this codeproject. http://www.codeproject.com/KB/aspnet/CacheInterview2.aspx?display=Print

    Thanks,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Larcolais Gong Wednesday, October 5, 2011 5:08 AM
    Thursday, September 29, 2011 9:18 AM
  • Thanks Larcolais!
    Eran.
    Friday, October 7, 2011 4:51 PM