Trigger Unhappy

reyboy · June 14th, 2005, 04:20 AM

I have a Triggers in my table UpdateMRPRed(Insert/Update)

Code:

'--------------
'This is my code in SQL Server
'--------------
CREATE TRIGGER UpdateMRPRed ON dbo.mat_tblPurchaserequisitiondet
FOR UPDATE, INSERT
AS
UPDATE srv_tblMaterialsReqDet
SET srv_tblMaterialsReqDet.PRed = (SELECT SUM(QTY) FROM mat_tblPurchaserequisitiondet 
WHERE MRNo = srv_tblMaterialsReqDet.MRNo AND StockCode = srv_tblMaterialsReqDet.StockCode)
WHERE  (srv_tblMaterialsReqDet.StockCode = (SELECT TOP 1 StockCode FROM inserted)) 
AND (srv_tblMaterialsReqDet.MRNo = (SELECT TOP 1 MRNo FROM inserted))

'--------------
'This is my code in Visual Basic
'Scenario: In a master-detail form, recordset RSPurchReqDet is bound to a Datagrid (Detail) and is saved only after the master has been saved
'--------------
   Dim i As Integer
   With RSPurchReqDet
      If Not .RecordCount = 0 Then
         .MoveFirst
         For i = 1 To .RecordCount
            .Fields(0) = txtPRNo
            On Error Resume Next
            .UpdateBatch adAffectCurrent ' Error Occurs in this part on connectionstring based 
            .MoveNext
         Next
         On Error Resume Next
         .UpdateBatch
         ConnExec
      End If
   End With
---------------------------------------------------

Case:
    If I manually input in SQL Server table, Trigger is OK
    If I use a DSN ("DSN=Materials") for ADODB.Connection, Trigger is OK
    If I use a Connectionstring for ("mConnStr=Provider.....") ADODB.Connection, Trigger Creates multiple copy of the row

Also
    How do I add speed to my trigger? Takes atleast 5 seconds for a row to save with a trigger and less than a second for a row without the trigger. Is Speed the Cost of using Trigger (1 table has 3 triggers)???

Proud To Be Pinoy

happygv · June 15th, 2005, 05:16 PM

Yes, obviously the speed is cost of the trigger, and if that is going to involve huge number of rows, then it would take its sweet time.

_________________________
- Vijay G
Strive for Perfection

reyboy · June 15th, 2005, 08:00 PM

thanks happygv, but how about this one?

Code:

"If I use a DSN ("DSN=Materials") for ADODB.Connection,  Trigger is OK    
 If I use a Connectionstring for ("mConnStr=Provider.....")  ADODB.Connection, Trigger Creates multiple copy of the row"

why does trigger behave in this manner?

Proud To Be Pinoy

happygv · June 16th, 2005, 12:37 PM

I have not come across this problem yet. Whichever approach it is with, it should work the same way. How about posting your code in comparision of both? Having a look at it might help.

I wonder how that happens, as I see only the UPDATE statement that you do in your trigger. If at all it is having multiple copy as you say, you should have used INSERT rather than UPDATE statement.

_________________________
- Vijay G
Strive for Perfection

reyboy · June 18th, 2005, 04:20 AM

Actually the App is running well currently using DSN connection, when one our developers tried to use connectionstring it was then that we found out that it had that problem.

So hard to believe that a simple change of connection type made us review the app from ground-up. I'll be posting all the saving codes tomorrow.
3 triggers, 2 code snippet from VB.

Proud To Be Pinoy