Strange happenings in sql server

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.
CamydenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ved Prakash AgrawalDatabase Consultant/Performance ArchitectCommented:
there is any trigger on that table ???????
0
Ved Prakash AgrawalDatabase Consultant/Performance ArchitectCommented:
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
dqmqCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

CamydenAuthor Commented:
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
Anthony PerkinsCommented:
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
Anthony PerkinsCommented:
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
dqmqCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.