Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a transactional-replication running in my database, and I need to execute a service on my website that refreshes the cache every time the database changes.

So i guess i have two options to make it work.

  1. Execute job when transactions occur
  2. Execute a job on every database change

But i have no idea how to do either of those. Is it even possible? I have been looking around and it seems that the "on-transaction" event, is not really possible because the replication agent is not starting and stopping, it's just running the whole time.

And executing a job on every database change should be possible, but again, never tried it before, but i really would bet money on it being possible.

Anyone know how this can be done? Thanks in advance :)

EDIT:

I have two sets of servers, two webservers and two database servers. The db-server has transactional replication to the other db.

Each webserver has it's own website running with some pretty deep caching. I need to trigger a cacherefresh webservice on the webserver so that the replicated changes will be visible to users.

Yesterday after writing this question, i set up a scheduled job to refresh the cache, and if this works wothout any hicks, i would rather keep that solution going, but if not i will need to trigger the refresh some other way.

Thank you :)

share|improve this question
    
Could you explain more about the "every time database changes"? Are you referring to data changes in the source tables? –  Sky Oct 30 '12 at 0:34
2  
This sounds, um, dangerous. You want to run a job after every data-changing transaction in your database? –  Aaron Bertrand Oct 30 '12 at 3:06
    
You are right, this could easily backfire without some kind of throttling –  Moulde Oct 30 '12 at 8:42
    
This question seems similar to this: stackoverflow.com/questions/8658629/… –  darin strait Oct 30 '12 at 14:15
add comment

1 Answer 1

up vote 1 down vote accepted

In sql server database level triggers comes in handy like this situation , you can call job to start and leave inside the trigger like

CREATE TRIGGER dbo.TableName_IUD
ON dbo.TableName
AFTER INSERT, UPDATE, DELETE
AS 
BEGIN
    SET NOCOUNT ON;
    EXEC dbo.sp_start_job N'jobname'
END
share|improve this answer
    
This seems like a way to do it, this may be the way i chose to do it if the current more simple solution doesn't work out. –  Moulde Oct 30 '12 at 8:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.