8.Create a trigger for the Invoices table that automatically inserts the vendor

Question

8.Create a trigger for the Invoices table that automatically inserts the vendor name and address for a paid invoice into a table named ShippingLabels. The trigger should fire any time the PaymentTotal column of the Invoices table is updated. The structure of the ShippingLabels table is as follows: CREATE TABLE ShippingLabels (VendorName varchar(50), VendorAddress1 varchar(50), VendorAddress2 varchar(50), VendorCity varchar(50), VendorState char(2), VendorZipCode varchar(20)) Use this UPDATE statement to test the trigger: UPDATE Invoices SET PaymentTotal = 67.92, PaymentDate = '2008-08-23' WHERE InvoiceID = 100

Explanation / Answer

Create Trigger InvoiceUpdate
ON
Invoices
AFTER UPDATE
AS

declare @invoiceid as int

select @invoiceID = InvoiceID
from Inserted

if exists (select 1 from ShippingLabels where InvoiceID = @InvoiceID)
begin
UPDATE ShippingLabels
Set VendorName = Vendors.VendorName,
VendorAddress1 = Vendors.VendorAddress1
FROM Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
WHERE InvoiceID = @InvoiceID
end
else
begin

Insert into ShippingLabels

...

end

Related Questions

Navigate

• Browse (All)
• Browse 8
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.