question

rawilken avatar image
rawilken asked

T SQL trigger not functioning

I have two stored procedures... #1: Create a stored procedure to calculate the price of a charter and update the row in the Charter table. The stored procedure takes a @CharTrip (numeric) parameter. This parameter is the primary key to the Charter table. The price of a Charter is determined by: The mileage charge for the Model for the Aircraft used in the charter. This is multiplied by the miles flown for the charter. There is a 79 cent per gallon fuel charge for every gallon used by the charter flight There is a wait charge of $112.50 for each hour that the charter is on the ground waiting. The sum of these charges is the price for the charter. The per gallon surcharge and the hour wait charge should be prominent in the stored procedure so that they can be easily changed by a T-SQL analyst when the business conditions change. CREATE PROCEDURE spCharterPrice (@CharTrip numeric) AS DECLARE @FuelCharge money = 0.79, -- VARIABLE PER GALLON FUEL RATE @WaitCharge money = 112.50 -- VARIABLE PER HOUR WAIT RATE BEGIN SELECT C.CHAR_TRIP, CAST(ROUND(SUM(([MOD_CHG_MILE] * [CHAR_DISTANCE]) + ([CHAR_FUEL_GALLONS] * @FuelCharge) + ([CHAR_HOURS_WAIT] * @WaitCharge)), 2) AS decimal(6, 2)) AS Price FROM AIRCRAFT A INNER JOIN CHARTER C ON A.AC_NUMBER = C.AC_NUMBER INNER JOIN MODEL M ON A.MOD_CODE = M.MOD_CODE WHERE C.CHAR_TRIP = @CharTrip GROUP BY C.CHAR_TRIP, C.AC_NUMBER END And... #2: Create a stored procedure to calculate the history of an aircraft and update the row in the Aircraft table. The stored procedure takes an @AC_NUMBER (Char (5)) parameter. This history includes: The number of trips The distance flown The hours flown Number of gallons of fuel used The number of quarts of oil used CREATE PROCEDURE spAircraftHistory (@AC_NUMBER char(5)) AS 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 Now I want to create a Trigger that calls them for the following scenarios... #3: 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. Here is what I have and it is not adding up the values for the Trips, Distance, etc accurately and it is not giving me all the records. CREATE TRIGGER Rodd.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.HoursFlown, 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 HoursFlown, 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 END
t-sqltriggerhomework
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
WilliamD avatar image
WilliamD answered
Rawilken, first of all, you are not using the @AC_NUMBER variable that you set to filter in any way - is this intended? Second; you are setting @AC_NUMBER incorrectly. By doing `SELECT FROM INSERTED` and assigning the value to a variable, you will get any value back from the inserted pseudo table if more than one insert occurs. The same goes for the `SELECT FROM DELETED` The update on CHARTER does use a join for the sub-query, I don't think that can be right, you need to join back on the charter number, or am I wrong?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.