T-SQL Trigger

Question

0.00/5 (No votes)

See more:

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.

[edit]Formatted text for readbility and code snipppets[edit]

Posted 11-Dec-10 8:39am

rawilken

Updated 11-Dec-10 10:31am

Henry Minute

v3

Add a Solution

Comments

fjdiewornncalwe 11-Dec-10 17:06pm

Ok then, where have you gotten to with the trigger. You have done quite well with the procedures, but without telling us what issues you are having with the trigger, we can't really help, and no one here should be doing it for you.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

rawilken · Answer 1 · 2010-12-14T12:58:00

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