SQL Server 7.0 And VB Trigger Events

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

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:

Code:

---

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

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