Could someone please help me rewrite these triggers as stored procedures.
Thanks
Trigger 1
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
Trigger 2
ALTER TRIGGER trInvoiceDetails_IU_UpdateMiscQty
ON dbo.InvoiceDetails
FOR INSERT, UPDATE
AS
/* IF UPDATE () ...*/
IF @.@.ROWCOUNT = 0
RETURN
IF UPDATE (Qty)
UPDATE MiscInventory
SET MiscInventory.InStock = ( MiscInventory.InStock - i.Qty )
FROM inserted i
JOIN MiscInventory
ON i.ItemID = MiscInventory.ItemID and i.ItemNumber=MiscInventory.ItemNumber
Trigger 3
ALTER TRIGGER trPayment_UD_InvoiceTotal
ON dbo.Payments
FOR INSERT, UPDATE
AS
IF UPDATE (Payment)
UPDATE Invoices
SET Invoices.InvoiceTotal= ( Invoices.InvoiceTotal - i.Payment )
FROM inserted i
JOIN Invoices
ON i.InvoiceID = Invoices.InvoiceID
Why do you want to do that?
You won't be able to access inserted/deleted in an SP but you can insert the contents into a temp table and access that.
Your trPaidInvoices is odd - it works on the whole table and does a select at the end!
Also does this work? If it's an update you should reverse the old value and add the new unless the update is always frmo 0.
Does if update work for an insert? I think it doesn't catch one of insert and delete not suer which.
Also what if someone updates to the same value?
|||I was told it was "safer" to use sp instead of triggers.
The triggers work fine actually just want to know how to do the same with sp's
( the select on the trPaidInvoices is just my typo copied from the designer)
Cam
No comments:
Post a Comment