First Trigger Need Help

I apologize in advance for the long post but want to give all the details i can.

I have read everything I can find (BOL, Google'd) about triggers and I am still having problems with writing this.

First off I add rows to "invoicedetails" table by selecting rows from Iventorydatagridview with this code.

Dim dt As DataTable = Me.RCSDataSet.Tables("invoicedetails")

Dim dtRow As DataRow = dt.NewRow()

dtRow("CustomerID") = dgvRow.Cells("CustomerID").Value

dtRow("InvoiceID") = dgvRow.Cells("InvoiceID").Value

dtRow("ItemNumber") = dgvRow.Cells("ItemNo").Value

dtRow("ItemID") = dgvRow.Cells("ItemID").Value

dtRow("Description") = dgvRow.Cells("ItemDescription").Value

dtRow("Units") = dgvRow.Cells("Units").Value

dtRow("Price") = dgvRow.Cells("Price").Value

dt.Rows.Add(dtRow)

Next

That works like a charm.

Next I had to figure out a way to "qty" value in invoicedetails table from "instock" value in inventory table. So, I tried it with a stored procedure.

ALTER PROCEDURE UpdateItemQty1

@.itemid int,

@.Qty INT

AS

DECLARE @.InStock INT;

UPDATE Inventory

SET InStock = Inventory.InStock - @.QTY

FROM Inventory

WHERE (Inventory.ItemID = @.itemid)

SELECT InStock, ItemNo

FROM Inventory

which i called with

' Dim Itemid As Integer = InventoryDataGridView.Item(3, InventoryDataGridView.CurrentRow.Index).Value

' Dim Qty As Integer = InvoiceDetailsDataGridView.Item(6, InvoiceDetailsDataGridView.CurrentRow.Index).Value

' Dim connection As New SqlClient.SqlConnection(My.Settings.RCSConnectionString)

' Dim itemqtyCommand As SqlClient.SqlCommand = New SqlClient.SqlCommand("UpdateItemQty1", connection)

' itemqtyCommand.CommandType = CommandType.StoredProcedure

' itemqtyCommand.Parameters.Add(New SqlClient.SqlParameter("@.itemid", SqlDbType.Int)).Value = Itemid

' itemqtyCommand.Parameters.Add(New SqlClient.SqlParameter("@.qty", SqlDbType.Int)).Value = Qty

' connection.Open()

' itemqtyCommand.ExecuteNonQuery()

' connection.Close()

This really gave me trouble. It would only work if i add one row to invoice details then update. If I added more than one row to InvoiceDetails then it would only update the "instock"value of the inventory table for the last row added in invoicedetails.

So then I decided to try a trigger on invoicedetails insert but for the life of me I cant figure out how the thing should be written, I know i have to have a join on the tables but can't figure it out.

I would like the trigger to update the inventory "instock" value based on the inserted "invoiecdetails" rows by invoiceId

I would be very grateful for any help to get over this hurdle.

First, a disclaimer. Keeping values calculated from other values in the database is considered not a good idea. It is too easy for the calculated values to become de-syncronized from the underlying data. Often, a view is created that will provide the calculated values upon need.

A TRIGGER has to be able to handle multiple rows inserted, updated, and/or deleted. Fortunately, there are two virtual tables available only during the operation of the TRIGGER. Those virtual tables are named [inserted] and [deleted]. They will contain both all new values and all removed values. So writing a TRIGGER often entails using those tables. The following will provide you a sense of the form and functionality of a TRIGGER (No guarentees that it works since I don't have the table DDL or sample data.)

CREATE TRIGGER trInvoiceDetails_IU_UpdateQty
ON InvoiceDetails
FOR INSERT, UPDATE
AS
IF @.@.ROWCOUNT = 0
RETURN
IF UPDATE (Units)
UPDATE Inventory
SET Inventory.InStock = ( Inventory.InStock - i.Units )
FROM inserted i
JOIN Inventory
ON i.ItemID = Inventory.ItemID

And then you will need a similar Trigger to add the values back into Inventory on a DELETE.

|||Thank you very much exactly what i was looking for.