Wednesday, March 21, 2012

rewrite triggers as stored procedures

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