In general, this is not the recommended usage of memcached. The best way is to have an object relational mapper that you handle all database access through and put your caching in that layer. That way you'll have cached objects or lists of objects above it, and you should be able to do all invalidations and updates in that layer.
You can of course do your invalidations in the database, but chances are that you lack the bigger picture at that level, you won't know exactly which data needs to be invalidated and what the cache keys for that data are. Also, the higher you move your caching, the more complex and aggregated data you can cache. Therefore, the general recommendation is to cache objects, not database rows. As for your actual question, I'm not familiar with calling .Net code from db triggers, but how does it handle the runtime? Will the db only spawn one CLR or multiple? The BeIT Memcached client has somewhat of a startup cost and it has a connection pool, so it works best if the database only keeps one CLR for all triggers. /Henrik On Fri, Oct 2, 2009 at 12:00, Adi <[email protected]> wrote: > > All, > I am using memcached on windows server 2003 and using Be.IT memcached > client. > > How can i remove cache object when any direct database change occur, i > want to invalidate / remove cache objects from memcached server, for > this i am thinking about the CLR stored procedure. > > What if i write a trigger that activate on Update/Delete evetns and > call the CLR stored procedure to remove cache objects? somebody tried > this thing before? > > Thanks >
