[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 180
  • Last Modified:

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.
0
Camyden
Asked:
Camyden
  • 2
  • 2
  • 2
  • +1
1 Solution
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now