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, 2010 at 11:13 AM in Default

avatar image

rawilken
101 10 11 11

and what is the question.........................?

Dec 08, 2010 at 11:22 AM Kev Riley ♦♦

@Kev - the question is... why has someone voted up this question?

Dec 09, 2010 at 12:21 AM ThomasRushton ♦♦

@Thomas - that has made me smile.........

Dec 09, 2010 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?

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. :-)

more ▼

answered Dec 08, 2010 at 11:29 AM

avatar image

Pavel Pawlowski
22.7k 10 15 26

(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.

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:

x1065
x139
x96

asked: Dec 08, 2010 at 11:13 AM

Seen: 2065 times

Last Updated: Dec 08, 2010 at 11:25 AM

Copyright 2016 Redgate Software. Privacy Policy