question

-- ============================================= -- Write an insert, delete and update trigger named -- trgCharterUpdate for the Charter Table. These will -- update the Price column on the Charter table and -- the Number of Trips, Distance Flown, Hours Flown, -- Fuel and Oil Used on the Aircraft table. Be aware -- that a charter can change the aircraft which will -- affect two aircrafts--there will be a trigger fired for each. -- ============================================= CREATE TRIGGER trgCharterUpdate ON CHARTER AFTER INSERT, UPDATE, DELETE NOT FOR REPLICATION AS IF @@ROWCOUNT > 0 BEGIN DECLARE @ActionType int , @RowCount int , @AC_NUMBER nvarchar(50) , @FuelCharge money = 0.79 -- VARIABLE PER GALLON FUEL RATE , @WaitCharge money = 112.50 ; -- VARIABLE PER HOUR WAIT RATE SET @ActionType = 0 ; SELECT @RowCount = count(*) FROM deleted ; IF @RowCount>0 SET @ActionType = 1 ; SELECT @RowCount = count(*) FROM inserted ; IF @RowCount > 0 SET @ActionType = @ActionType + 2 ; IF @ActionType = 1 SELECT @AC_NUMBER = AC_NUMBER FROM deleted ; ELSE SELECT @AC_NUMBER + AC_NUMBER FROM inserted ; UPDATE CHARTER SET C.PRICE = foo1.Price FROM ( SELECT CAST(ROUND(SUM (([MOD_CHG_MILE]*[CHAR_DISTANCE]) + ([CHAR_FUEL_GALLONS]* @FuelCharge) + ([CHAR_HOURS_WAIT]* @WaitCharge)),2) AS decimal(6,2)) AS Price FROM CHARTER C GROUP BY C.CHAR_TRIP, C.AC_NUMBER ) AS foo1 UPDATE AIRCRAFT SET TripCount = foo.Trips , DistanceFlown = foo.Distance , HoursFlown = foo.Hours , FuelUsed = foo.Fuel , OilUsed = foo.Oil FROM AIRCRAFT A INNER JOIN ( SELECT AC_NUMBER , Count(C.CHAR_TRIP) AS Trips , Sum(C.CHAR_DISTANCE) AS Distance , Sum(C.CHAR_HOURS_FLOWN) AS Hours , Sum(C.CHAR_FUEL_GALLONS) AS Fuel , Sum(C.CHAR_OIL_QTS) As Oil FROM CHARTER C GROUP BY AC_NUMBER ) AS foo ON A.AC_NUMBER = foo.AC_NUMBER CASE @ActionType WHEN 1 THEN 'Delete' WHEN 2 THEN 'Insert' WHEN 3 THEN 'Update' END