T-SQL触发器 [英] T-SQL Trigger

问题描述

我有两个存储过程...
#1:

创建一个存储过程来计算租船价格并更新Charter表中的行.该存储过程采用@CharTrip(数字)参数.此参数是Charter表的主键.

包机的价格由以下因素决定:

包机中使用的飞机模型的里程费用.
这乘以包机飞行的英里数.
包机每使用一加仑汽油，将收取每加仑79美分的燃油费.
包机在地面上等待的时间为每小时$ 112.50.
这些费用的总和就是包机的价格.

每加仑附加费和小时等待费在存储过程中应突出显示，以便T-SQL分析师可以在业务条件更改时轻松更改它们.

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

还有...
#2:
创建一个存储过程来计算飞机的历史记录并更新飞机"表中的行.该存储过程采用@AC_NUMBER(Char(5))参数.此历史记录包括:

旅行次数
飞行距离
飞行的小时数
使用的燃料加仑数
使用的夸脱油数

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

现在，我想创建一个触发器，在以下情况下调用它们...
#3:
为宪章表编写一个名为trgCharterUpdate的插入，删除和更新触发器.这些将更新包机"表上的价格"列，以及飞机"表上的出行次数"，飞行距离"，飞行小时数"，燃料和机油".请注意，包机可以改变飞机，这将影响两架飞机-每架飞机都会被触发.

[edit]用于可读性和代码段的带格式文本[edit]

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]

推荐答案

每小时在宪章上等待的时间为112.50.
这些费用的总和就是包机的价格.

每加仑附加费和小时等待费在存储过程中应突出显示，以便T-SQL分析师可以在业务条件更改时轻松更改它们.

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

还有...
#2:
创建一个存储过程来计算飞机的历史记录并更新飞机"表中的行.该存储过程采用@AC_NUMBER(Char(5))参数.此历史记录包括:

旅行次数
飞行距离
飞行的小时数
使用的燃料加仑数
使用的夸脱油数

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

现在，我想创建一个触发器，在以下情况下调用它们...
#3:
为宪章表编写一个名为trgCharterUpdate的插入，删除和更新触发器.这些将更新包机"表上的价格"列，以及飞机"表上的出行次数"，飞行距离"，飞行小时数"，燃料和机油".请注意，包机可以改变飞机，这将影响两架飞机-每架飞机都会被触发.

[edit]用于可读性和代码段的格式化文本

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]

创建触发器Rodd.trgCharterUpdate

宪章》

插入，更新，删除后

不得复制

AS

如果@@ ROWCOUNT> 0

开始

DECLARE @ActionType int
，@RowCount int
，@AC_NUMBER nvarchar(50)
，@FuelCharge money = 0.79-每加仑燃油价格可变
，@WaitCharge金钱= 112.50; -每小时收费变动率

SET @ActionType = 0;

SELECT @RowCount =计数(*)
FROM已删除;

如果@RowCount> 0
SET @ActionType = 1;

SELECT @RowCount =计数(*)
从FROM插入;

如果@RowCount> 0
SET @ActionType = @ActionType + 2;

IF @ActionType = 1
SELECT @AC_NUMBER = AC_NUMBER
FROM已删除;

ELSE
SELECT @AC_NUMBER = AC_NUMBER
从FROM插入;


更新包
SET C.PRICE = foo1.Price
从(
SELECT CAST(ROUND(SUM(([[MOD_CHG_MILE] * [CHAR_DISTANCE])+([[CHAR_FUEL_GALLONS] * @FuelCharge)+([CHAR_HOURS_WAIT] * @WaitCharge))，2)AS十进制(6,2))AS价格

从宪章C
GROUP BY C.CHAR_TRIP，C.AC_NUMBER
)
AS foo1

更新飞机

SET TripCount = foo.Trips
，DistanceFlown = foo.Distance
，HoursFlown = foo.HoursFlown
，FuelUsed = foo.Fuel
，OilUsed = foo.Oil

从飞机A
内联接
(
SELECT AC_NUMBER
，Count(C.CHAR_TRIP)AS行程
，Sum(C.CHAR_DISTANCE)AS距离
，Sum(C.CHAR_HOURS_FLOWN)AS HoursFlown
，Sum(C.CHAR_FUEL_GALLONS)AS Fuel
，总和(C.CHAR_OIL_QTS)作为油
从宪章C
GROUP BY AC_NUMBER
)
AS foo

ON A.AC_NUMBER = foo.AC_NUMBER

END

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-SQL触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！