question

rawilken avatar image
rawilken asked

T-SQL Insert Delete Update function

-- ============================================= -- 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
t-sqltriggerhomework
3 comments
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.

and what is the question.........................?
3 Likes 3 ·
@Kev - the question is... why has someone voted up this question?
1 Like 1 ·
@Thomas - that has made me smile.........
0 Likes 0 ·

1 Answer

· Write an Answer
Pavel Pawlowski avatar image
Pavel Pawlowski answered
Study my answer to your previous question [Triggers - Where am I going wrong?][1] It's only a small modification of your previous problem. If you will study and try to understand it, you will also solve this problem. I think, we cannot solve all your homeworks. :-) [1]: http://ask.sqlservercentral.com/questions/29371/triggers-where-am-i-going-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.