>The app I'm working on uses VFP triggers to allow or prevent changes based on the existance of a particular object to ensure the changes are being made thru the system (don't start me on the wisdom of that...I inherited this mess). We are trying to migrate the system to use SQL Server 7.0 as the back end, and the powers-that-beat want to carry over this design "feature".
>
>Unfortunately nothing comes immediately to mind. We have already build a class that passes changes to SQL directly. It wouldn't be too difficult to create a middle-tier COM object to handle data changes, but can a SQL Server trigger communicate with an external object? Anyone have any suggestions on how to do this?

This may or may not help you but SQL Server 7 has several store procedures that enable OLE Automation with SPs and Triggers.

sp_OACreate - Create an instance of an OLE COM object
sp_OAGetProperty - retrieve a property setting
sp_OASetProperty - set a property setting
sp_OAMethod - execute a method of the object
sp_OADestroy - get rid of the OLE COM object
sp_GetErrorInfo - the name says it all

You could create an instance of your COM object in a trigger and communicate all you want. Unfortunately there is no spOAGetObject so you have to create a new instance every time.

Is this what you were looking for?

Although, I have a question about this. I can see where the initial data management would be handled by the middle-tier object. It could check for the existence of an object (or one of its properties) and then update the back-end data. But why would the trigger have to access this information? IMO, the trigger should handle data releated items. Once the business object has determined an update should take place, the trigger should simply run.