Sql-server – Execute job every time database changes – execute job on each change-event

sql serversql-server-2008sql-server-2012

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 🙂

Best Answer

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
Related Question