I'm searching for a nice way to get a C# notification if a new row is inserted in a table. The table will be filled by an other closed application and I can't get any information before the item is written to SQL. I only need the ID from the new row.
I already have a running solution. It is possible to use the SqlDependency based on service broker. But for this I have to use a base query like: SELECT [ID] FROM dbo.mytable
For big tables it is not a proper solution...
Maybe I can use an insert trigger and update a status table that only holds one row with the newest ID. But I think there must be a better solution?
Where are the SQL/C# professionals? Has someone the way of best practice?