Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
INSTEAD OF Triggers
Message
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Autre
Versions des environnements
SQL Server:
SQL Server 2008
Divers
Thread ID:
01467449
Message ID:
01467454
Vues:
48
Well, this is what I ended up with for the trigger from the other day or night and it works perfectly, now, but it took me a while to "get it" I suppose some people get it faster than others. I am not in the faster crowd, unfortunately. It helps if you work with it a lot in your job. The below trigger uses a database called AP.
-- 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
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform