I need help to create a trigger for the Invoices table that automatically insert

Question

I need help to 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 for an insert or update operation, but a row should be added to the ShippingLabels table only if the invoice balance due is zero. 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 TABLE ShippingLabels (VendorName varchar(50), VendorAddress1 varchar(50), VendorAddress2 varchar(50), VendorCity varchar(50), VendorState char(2), VendorZipCode varchar(20)) Trigger: CREATE TRIGGER Invoices_UPDATE_Shipping ON ShippingLabels AFTER INSERT, UPDATE AS INSERT ShippingLabels SELECT VendorName, VendorAddress1, VendorAddress2, VendorCity, VendorState, VendorZipCode FROM Vendors JOIN ShippingLabels ON Vendors.VendorID = (SELECT VendorID FROM VENDOR) WHERE Inserted.InvoiceTotal - Inserted.PaymentTotal- Inserted.CreditTotal = 0

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

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