How can I create an INSTEAD OF trigger that returns results to the calling stored procedure? And how does this stored procedure call the trigger?
In SQL Server, a calling stored procedure can't directly invoke a trigger. Instead, SQL Server invokes a trigger when data on which the trigger is created is changing. However, any trigger can return a result set to a client application. The key is that the application has to process the results, as Listing 2 shows. In general, you should avoid returning results from triggers because doing so requires that you explicitly code your application to expect what would typically be unexpected results.