SQL Server 7.0 And VB Trigger Events

[Clunietp]

I'm pretty sure you can't raise an event from SQL Server to VB...

your best option might be to create a trigger for each insert/update/delete, and do what you need to do (if you can) in that trigger.

I can see how using a timed VB app to query the database would put unnecessary strain on your DBMS

[S@NSIS]

Hi,
Thanks for your reply. Fortunately we have found an answer to this and thought you might like to know.

You can use a system stored procedure(),and then through OLE automation raise a method/event in a COM+ component and/or MTS object.
We are also looking at using MSMQ in COM+ to ensure that events reach their destination.

For instance:

DECLARE @object int
DECLARE @hr int
EXEC @hr = sp_OACreate 'my_project.my_class', @object OUT
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END

EXEC @hr = sp_OAMethod @object, 'myFunction'
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END


Hopefully we will crack it as your quite correct when you say the timer calls to the database create unnecessary strain!!

Shaun

[Clunietp]

ah yes, the sp_OACreate function....

you'll still have to create a trigger to handle this, and your db changes will be pretty slow, as instantiating COM components with COM+/MTS Context wrappers can consume a bit of memory...

another option might be to create a trigger to log all changes to a separate table, then every few minutes (or whatever time is required) have the SQL Server Agent execute a VB app. The VB app would then connect to the separate change table, do what is has to do, then empty the table out. Food for thought