Sql server trigger to .Net call
Whenever a record is inserted or updated in a sql server table, I have to call a third party service which will insert/update their table. What is the most efficient way to do this?
SQL server trigger to .Net call (I’m not sure if this is possible… Is it possible to send the id of the record to a .Net application whenever insert/update occurs?)
- How to keep Stored Procedures and other scripts in SVN/Other repository?
- Invalid Column Name Exception on Entity Framework
- What is this pattern in SQL
- Matching criteria for the below scenario
- Unhandled Exception: System.Runtime.InteropServices.COMException (0x800A03EC)
- SQL Server Datetime Subquery Conversion Error?
An application which checks continuously for changes in the table using a select statement, get the particular record -> send through service.
2 Solutions collect form web for “Sql server trigger to .Net call”
In SQL Server 2005 and above, you have the ability to create CLR Stored Procedures, which are stored procedures that run standard .Net code, as opposed to SQL related code. There are some limitations to what it can do, but you could easily create an insert trigger that passes the @@IDENTITY of the new record added to a CLR stored procedure that does the .Net processing you need.
A long time ago I worked on an application that
for each “data” table
- Had a history table that record all inserts, deletes and changes
- Each ‘logical’ transaction had a history ID, that could be used to find it’s changes across all the history tables
- The history IDs were ints that increased in value, so a 3rd party system to find all changes since it’s last check by doing a select from XyxHistory where historyId > lastProcessHistoryId
- Trigger were used to update the history tables
- Most of the above was generated using codesmith from the data tables
This allowed many 3rd party systems to be integrated without having to change the main application code.