Query on multi row trigger

[SpiritOfLennon]

Hi,
I'm trying to write a trigger that calls a VB6 program. I've got about this far

create TRIGGER scheme.trigger
ON owner.dbo.table
before INSERT, DELETE
AS
begin
declare @ni int, @nd int
declare @cmd varchar (8000)

select @ni = count(*) from Inserted
select @nd = count(*) from Deleted

-- DELETE
if @ni > @nd
SET @cmd = 'prwo del'
exec master.dbo.xp_cmdshell @cmd

--INSERT

if @ni < @nd
SET @cmd = 'prwo ins'
exec master.dbo.xp_cmdshell @cmd


END
GO

However I need to pass the details of the records affected to the VB6 program. I'm guessing that I should set up a loop that plows through the records in Deleted and Inserted, however I'm not sure how to do this. Here's my questions
1) Is there a logical way to move through the records in turn and pass them to the external program?
2) Is there a way to actually pass the rows through to the VB6 code?
3) Would the VB6 code have access to the tables Deleted and Inserted and be able to pick up the relevant data itself?

Excuse the dumb questions but I generally work with Oracle but a customer has asked me to migrate an Oracle application I have into SQL server. Any help would be greatly appreciated.
Oh and I don't want to code all my logic directly into the triggers for security reasons.
Thanks
SOL

 

[SQLSister]

Well I can't say that I think this is a good idea. Generally you want to do all the processing in the trigger. One of my concerns would be that if you have a failure in the vb prgram, the insert, update or delete would not be rolled back. So you could end up with dat integrity problems. This process is also bound to be less efficient because you will have to operate on a row by row basis.

However, in answer to your questions. Question 3 is definitely no. Nothing except the trigger can directly access the inserted or deleted pseudotables.

If you must row process you can use a cursor or a loop. Putting these in a trigger will slow things down, but I can't think of any way you can send the whole resultset to the vb program directly. A possible thing to do might be just to write the trigger to send the information to a holding table and then have the vb program run on a schedule against the holding table. I'm not sure exactly what you want to do with the data in the program or how real time it must be, so I don't know if this is feasible.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[SpiritOfLennon]

Thanks for the advice SQLSister.
I must admit I don't like the idea of doing it this way either but here's the background.
The program I'm writing is basically a bespoke modification to a vanilla application. The data needs to be pretty much real time so the only way to easily interface is to use triggers and then embed the logic required to populate additional tables. However I need the code to be inaccessible to other parties that may "appropriate" the logic for their own purposes.
I'm open to suggestions at this stage...


SOL
I'm only guessing but my guess work generally works for me.