-- 1. Create a trigger for the Invoices2 table that automatically -- inserts the vendor name and address for a paid invoice (hint: -- change in PaymentTotal) into a table named ShippingLabels. The -- trigger should fire any time the PaymentTotal column of the -- Invoices2 table is updated. -- a. Create the table ShippingLabels in the AP database first. -- 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)) -- b. Create the table Invoices2 from Invoices. If it exists, -- drop the table first. IF OBJECT_ID('Invoices2') IS NOT NULL DROP TABLE Invoices2 SELECT * INTO Invoices2 FROM Invoices -- c. Create the trigger with the description in the exercise. CREATE TRIGGER trInvoices2PaymentTotal_UPDATE ON Invoices2 AFTER UPDATE AS DECLARE @VendorID int, @VendorName VARCHAR(50), @VendorAddress1 VARCHAR(50), @VendorAddress2 VARCHAR(50), @VendorCity VARCHAR(50), @VendorState CHAR(2), @VendorZipCode VARCHAR(20) IF EXISTS -- Test whether PaymentTotal changed. (SELECT * FROM Deleted JOIN Invoices2 ON Deleted.InvoiceID = Invoices2.InvoiceID WHERE Deleted.PaymentTotal <> Invoices2.PaymentTotal) BEGIN -- Get the VendorName and the contact info into -- variables from the Invoices2 and Vendors tables. SELECT @VendorName = Vendors.VendorName, @VendorAddress1 = VendorAddress1, @VendorAddress2 = VendorAddress2, @VendorCity = VendorCity, @VendorState = VendorState, @VendorZipCode = VendorZipCode FROM Invoices2 JOIN Vendors ON Invoices2.VendorID = Vendors.VendorID JOIN Deleted ON Vendors.VendorID = Deleted.VendorID -- Now, insert the data for the ShippingLabel. INSERT ShippingLabels (VendorName, VendorAddress1, VendorAddress2, VendorCity, VendorState, VendorZipCode) VALUES (@Vendorname, @VendorAddress1, @VendorAddress2, @VendorCity, @VendorState, @VendorZipCode) END -- d. Test the trigger by updating PaymentTotal to 503.2 for -- InvoiceID 105. Verify the trigger result by listing the -- contents of ShippingLabels table. UPDATE Invoices2 SET PaymentTotal = 503.20 WHERE InvoiceID = 105 SELECT * FROM ShippingLabels