Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 14th, 2005, 04:20 AM
Authorized User
 
Join Date: Jul 2003
Location: Davao, , Philippines.
Posts: 71
Thanks: 0
Thanked 0 Times in 0 Posts
Default Trigger Unhappy

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
__________________
Proud To Be Pinoy
Reply With Quote
  #2 (permalink)  
Old June 15th, 2005, 05:16 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #3 (permalink)  
Old June 15th, 2005, 08:00 PM
Authorized User
 
Join Date: Jul 2003
Location: Davao, , Philippines.
Posts: 71
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old June 16th, 2005, 12:37 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #5 (permalink)  
Old June 18th, 2005, 04:20 AM
Authorized User
 
Join Date: Jul 2003
Location: Davao, , Philippines.
Posts: 71
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Trigger sumith ASP.NET 1.0 and 1.1 Professional 0 March 21st, 2007 12:44 AM
Trigger fhillipo SQL Server 2000 1 March 20th, 2007 07:44 AM
Trigger aware SQL Server 2000 1 April 15th, 2005 01:28 PM
Trigger realgone_ SQL Language 4 January 27th, 2005 11:49 AM
Unhappy student lost in arrary and cart with sessi cedtech23 Beginning PHP 2 July 30th, 2004 05:30 AM



All times are GMT -4. The time now is 01:29 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.