Trigger UNhappy - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Trigger UNhappy

Author

Topic

rb112978
Starting Member

11 Posts

Posted - 2005-06-14 : 05:16:28

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


'--------------
'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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources