mysterious changes of data

I have a simple invoice, inventory, billing program that is doing something strange but I can't track it down.

I can't figure out what triggers it but occasionally when I add an invoice to a customer all the other invoices are added to the last customer updated.

My update statement on the invoice table is

UPDATE Invoices
SET Date = @.Date, InvoiceTotal = @.InvoiceTotal, SubTotal = @.SubTotal, Tax = @.Tax, CustomerID = @.CustomerID
WHERE (CustomerID = @.CustomerID)

The only triggers on the table are to update the qty in Inventory and another trigger that fires when a payment is entered.

Any Ideas?

In your update statement, there is no need to update your customer

UPDATE Invoices
SET Date = @.Date, InvoiceTotal = @.InvoiceTotal, SubTotal = @.SubTotal, Tax = @.Tax, CustomerID = @.CustomerID
WHERE (CustomerID = @.CustomerID).

Could you post your trigger codes ?. Then only, we will find out the actual issue ?

Thanks.

Naras.

|||

Trigger on InvoiceDetails to update qty in inventory

ALTER TRIGGER trInvoiceDetails_IU_UpdateQty

ON InvoiceDetails

FOR INSERT, UPDATE

AS

IF @.@.ROWCOUNT = 0

RETURN

IF UPDATE (Qty)

UPDATE WInventory

SET WInventory.InStock = ( WInventory.InStock - i.Qty )

FROM inserted i

JOIN WInventory

ON i.ItemID = WInventory.ItemID and i.ItemNumber=WInventory.ItemNumber

Trigger on Invoices to close invoice when payment entered

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

|||

Hi,

shouldn′t that be:

ALTER TRIGGER trPaidInvoices

ON dbo.Invoices

FOR UPDATE

AS
BEGIN

IF UPDATE (InvoiceTotal)

insert into ClosedInvoices (InvoiceID,CustomerID,[Date])

SELECT InvoiceID, CustomerID, [Date]

FROM Invoices

WHERE (InvoiceTotal = 0.00)

NNER JOIN INSERTED I
ON I.InvoiceId = In.InvoiceId

Delete from invoices where invoiceTotal=0.00
FROM Invoices In
INNER JOIN INSERTED I
ON I.InvoiceId = In.InvoiceId

END

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||maybe but I don't see how that could be causing my original problem?|||The problem is not related to the posted trigger code. Perhpas there are some other triggers on the related tables which interfer with the invoices table.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

If you want to track this thing down quickly, perhaps SQL Profiler might help you to find out what statements are being run from where on what data to see exactly what is happening here and when.

Hope that helps,

John (MSFT)