February 9, 2012 at 1:03 pm
Without the use of triggers, is there a way to execute a stored procedure any time data change in user tables? The stored procedure is always the same one.
I'm looking for something like an event handler in .Net that also receives parameter values, but I'll settle for anything that does the trick. I'll need to know what data changed (table, primary key value) in order to pass that to the stored proc.
Thank you.
February 9, 2012 at 1:16 pm
peter 82125 (2/9/2012)
Without the use of triggers, is there a way to execute a stored procedure any time data change in user tables? The stored procedure is always the same one.I'm looking for something like an event handler in .Net that also receives parameter values, but I'll settle for anything that does the trick. I'll need to know what data changed (table, primary key value) in order to pass that to the stored proc.
Thank you.
This event handler you are describing, there is something like in sql. It is called triggers. That is what they do, they are event handlers for update, delete, insert to tables.
All kidding aside you could look at CDC but that doesn't execute a stored proc. Seems to me you want CDC instead of trying to roll your own. At least I am guessing that what you want is some level of auditing/history. Your brief description did not mention handling things like multiple row updates or composite keys. CDC can handle all this.
Read up on BOL. http://msdn.microsoft.com/en-us/library/bb522489.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 9, 2012 at 1:17 pm
any reason why you dont want to use a trigger? there are some articles out there that give some adive on how to do it without a trigger but i would say it all depends on the task, if your data is updated by stored procedures then can you not just add an additional insert in to that proc to insert into another audit table perhaps and then the same with any SP's that update the data.
***The first step is always the hardest *******
February 9, 2012 at 2:04 pm
We have these two databases: An SQL Server db and a non-SQL Mongo db. We need to update the Mongo db when data change in the SS database. We have a process that does that. It can be started by an http request. We want to make that http request whenever data change in the SS database.
I have a CLR stored proc in the SS database that makes the http request. I just need some triggering mechanism in there to run that proc. I'd hate to have to put the same call to the same proc in every table as triggers.
My question might boil down to this one: Is there a "central" location where data changes can be "sensed" and an action, such as executing a sp, can be taken (event/event handler type of thing)? CDC seems to do that, but it seems that I can't customize the handling of that event.
Thanks again.
February 9, 2012 at 2:08 pm
You might look into replication. It is designed to keep databases in sync (and they don't all have to be sql server). Otherwise I think you may be stuck with triggers.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 9, 2012 at 3:30 pm
This also might be a way to capture this. SQL Server triggers system events based on firing or executing procedures. You can use SQL Server (event) Broker to monitor for anyone firing that particular stored procedure, and initiate a process when that happens. This would do it asynchronously so you would not have the same magic tables you have during a trigger, but it would tell you someone ran the procedure which updates the data.
From BOL, the list of events you can create notifications on is here:
http://msdn.microsoft.com/en-us/library/ms175481.aspx
The event notification process is described here:
http://msdn.microsoft.com/en-us/library/ms178080.aspx
Service broker is reasonably painful to set up correctly, but works VERY nicely once you do.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply