x

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
more ▼

asked Dec 08 '10 at 11:13 AM in Default

rawilken gravatar image

rawilken
101 7 11 11

and what is the question.........................?
Dec 08 '10 at 11:22 AM Kev Riley ♦♦
@Kev - the question is... why has someone voted up this question?
Dec 09 '10 at 12:21 AM ThomasRushton ♦
@Thomas - that has made me smile.........
Dec 09 '10 at 01:58 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
more ▼

answered Dec 08 '10 at 11:29 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.2k 8 11 21

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x977
x114
x74

asked: Dec 08 '10 at 11:13 AM

Seen: 1613 times

Last Updated: Dec 08 '10 at 11:25 AM