?
Solved

Strange happenings in sql server

Posted on 2007-03-28
7
Medium Priority
?
175 Views
Last Modified: 2010-03-19
I'm hoping someone can help me with a strange problem.

I have an inventory/billing management application I've created with Visual Basic Express using SQL Express.  

Everythig works fine but I have a strange glitch happening.  

On the target machine after a little while.  All invoices that have been added to different customers will all move to one customer.  Somehow sql is changing the CustomerID in all invoices.

I'm having trouble duplicating this behavior on the development machine I was hoping someone could maybe give me ideas of why this would happen.

Thanks.
0
Comment
Question by:Camyden
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 11

Expert Comment

by:Ved Prakash Agrawal
ID: 18809143
there is any trigger on that table ???????
0
 
LVL 11

Expert Comment

by:Ved Prakash Agrawal
ID: 18809173
Here is steps to resolve your problem

1. Run Profiler
2. and execute your command by application.
3. stop the profiler.
4. check what entires are in the table if not correct  look back to profiler and check what value passed by application....and the profiler can give you a hint whats is wrong there....
0
 
LVL 42

Accepted Solution

by:
dqmq earned 1500 total points
ID: 18809319
Possibly an UPDATE with an indadequate WHERE clause.  One way to trace it down is with an after update trigger on the invoices table to trap that condition.

SELECT 1 FROM INSERTED INNER JOIN DELETED
ON INSERTED.InoviceID = DELETED.insertID
WHERE INSERTED.CustomerID <> DELETED.CustomerID
IF @@ROWCOUNT > 1
   BEGIN
   RAISERROR ('Mass update of customerID detected',15,1) WITH LOG
   ROLLBACK   --in case error is not trapped
   END
 
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:Camyden
ID: 18809453
1.The only trigger I have on Invoices Table is
ALTER TRIGGER trPaidInvoices
ON dbo.Invoices
FOR UPDATE
AS
      IF UPDATE (InvoiceTotal)
insert into ClosedInvoices (InvoiceID,CustomerID,[Date])
SELECT     InvoiceID, CustomerID, [Date]
FROM         Invoices
WHERE     (InvoiceTotal = 0.00)
Delete from invoices where invoiceTotal=0.00
select * from Invoices


2.Not sure how to run the profiler?
3. my invoices update statement is
UPDATE    Invoices
SET              CustomerID = @CustomerID, Date = @Date, InvoiceTotal = @InvoiceTotal, SubTotal = @SubTotal, Tax = @Tax
WHERE     (CustomerID = @CustomerID)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18810121
Totally unrelated, but the following line should not be in your trigger
select * from Invoices

You also may want to read up on Triggers.  You should only update the invoices that have changed not all the invoices in the whole table.  Specifically read up on the Inserted and Deleted logical tables in Triggers.


Also there is not much point in updating CustomerID with the same CustomerID, so I would re-write your Update statement as:
UPDATE    Invoices
SET              [Date] = @Date, InvoiceTotal = @InvoiceTotal, SubTotal = @SubTotal, Tax = @Tax
WHERE     CustomerID = @CustomerID
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18810145
Again while the following is not the direct problem, you may want to read up on structured logic using IF statements.  I suspect that you meant to write this:

IF UPDATE (InvoiceTotal)
   Begin
          insert into ClosedInvoices (InvoiceID,CustomerID,[Date])
          SELECT     InvoiceID, CustomerID, [Date]
          FROM         Invoices
          WHERE     (InvoiceTotal = 0.00)

          Delete from invoices where invoiceTotal=0.00
   End

Also you should understand that IF UPDATE does NOT mean that InvoiceTotal has actually changed, merely that it was involved in an UPDATE statement.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 18811942
I have other concerns with that trigger.  It can result in duplicate and/or lost closed invoice history unless the transaction is serialized. The problem occurs if the trigger fires mulitple times concurrently and the same invoice is inserted more than once before it is deleted or an InvoiceTotal changes to zero after the insert but before the delete and thus gets lost.

Carrying on from ACperkins, this would be better:

IF UPDATE (InvoiceTotal)
   Begin
          insert into ClosedInvoices (InvoiceID,CustomerID,[Date])
          SELECT     i.InvoiceID, i.CustomerID, i.[Date]
          FROM         Inserted i INNER JION deleted d on i.invoiceID = d.invoiceID
          WHERE     i.InvoiceTotal = 0 AND d.InvoiceTotal <> 0;

          Delete from invoices i where i.invoiceID in
            (select invoiceID from Inserted where invoiceTotal = 0);
   End

On second thought, there may still be some very-unlikely-to-happen holes in that logic. Beyond a doubt, the safest solution is to move the delete from that trigger to an AFTER INSERT trigger on the ClosedInvoice table.

--DQMiller



0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question