Новичок
Джуниор
Регистрация: 25.05.2016
Сообщений: 1
|
Неправильно работает триггер на удаление и запрет вставки в ms sql management studio
Здравствуйте!
Есть база данных Городской транспорт(на всякий случай прикладываю запрос на ее создание).
Пусть известна цена за литр топлива. Надо удалить все Route(маршруты), у которых цена за проезд(Route.Fare) менее чем в 1.5
превышает расход на топливо(TypeOfTransport.FuelConsOn1 00k). И не запретить их вставку.
Собственно пишу 2 триггера. На удаления и на вставку
Код:
USE [Городской транспорт3]
CREATE VIEW r_q AS
(SELECT Route.ID
FROM Route, TypeOfTransport
WHERE Route.Fare < 1.5 * TypeOfTransport.FuelConsOn100k)
CREATE TRIGGER del_route
ON r_q
INSTEAD OF DELETE
AS
DELETE FROM Route
WHERE Route.ID IN
(SELECT ID
FROM r_q)
CREATE TRIGGER LIMIT ON Route
INSTEAD OF INSERT AS
IF EXISTS(
SELECT INSERTED.ID
FROM INSERTED JOIN TypeOfTransport ON INSERTED.ID = TypeOfTransport.RouteID
WHERE INSERTED.Fare < 1.5 * TypeOfTransport.FuelConsOn100k
)
BEGIN
ROLLBACK
END
ELSE INSERT INTO Route SELECT * FROM inserted
INSERT INTO Route
(ID, WayID, Fare, NumberOfPassengersPerDay, NumberOfCarOnRoute) VALUES
(22, 1, 10, 409, 7)
SELECT *
FROM Route
DELETE FROM Route
SELECT *
FROM Route
Однако, при удалении возникает ошибка "Конфликт инструкции DELETE с ограничением REFERENCE "FK_TypeOfTransport_Route". Конфликт произошел в базе данных "Городской транспорт3", таблица "dbo.TypeOfTransport", column 'RouteID'."
А триггер на запрещение вставки все равно дает вставить строку
Код:
INSERT INTO Route
(ID, WayID, Fare, NumberOfPassengersPerDay, NumberOfCarOnRoute) VALUES
(22, 1, 10, 409, 7)
Пожалуйста, помогите мне с этим разобраться, сижу уже вторые сутки над этой задачей и не могу понять, почему оно не работает
Собственно, запрос на создание базы данных
Код:
DROP TABLE ModelOfType
DROP TABLE Driver
DROP TABLE TypeOfTransport
DROP TABLE Route
DROP TABLE Way
CREATE TABLE [Way]
(
[ID] [INT] IDENTITY(1,1) NOT NULL,
[StartingStation] [VARCHAR](100) NOT NULL,
[EndingStation] [VARCHAR](100) NOT NULL,
[Distance] [INT] NOT NULL,
[TheNumberOfStops] [INT] NOT NULL,
[EstimatedTimeEnRoute] [INT] NOT NULL,
CONSTRAINT [PK_Way] PRIMARY KEY ([ID] ASC)
);
CREATE TABLE [Route]
(
[ID] [INT] IDENTITY(1,1) NOT NULL,
[WayID] [INT] NOT NULL,
[Fare] [INT] NOT NULL,
[NumberOfPassengersPerDay] [INT] NOT NULL,
[NumberOfCarOnRoute] [INT] NOT NULL,
CONSTRAINT [PK_Route] PRIMARY KEY ([ID] ASC)
);
CREATE TABLE [TypeOfTransport]
(
[ID] [INT] IDENTITY(1,1) NOT NULL,
[RouteID] [INT] NOT NULL,
[AvarageSpeed] [INT] NOT NULL,
[Name] [VARCHAR](100) NOT NULL,
[AmountOfCarInPark] [INT] NOT NULL,
[FuelConsOn100k] [INT] NOT NULL,
CONSTRAINT [PK_TypeOfTransport] PRIMARY KEY ([ID] ASC)
);
ALTER TABLE [TypeOfTransport]
ADD CONSTRAINT [FK_TypeOfTransport_Route] FOREIGN KEY([RouteID])
REFERENCES [Route] ([ID]);
ALTER TABLE [TypeOfTransport]
ADD CONSTRAINT [CK_TypeOfTransport_AvarageSpeed]
CHECK ([AvarageSpeed] > 0);
ALTER TABLE [TypeOfTransport]
ADD CONSTRAINT [CK_TypeOfTransport_AmountOfCarInPark]
CHECK ([AmountOfCarInPark] >= 0);
ALTER TABLE [TypeOfTransport]
ADD CONSTRAINT [CK_TypeOfTransport_FuelConsOn100k]
CHECK ([FuelConsOn100k] >= 0);
ALTER TABLE [Route]
ADD CONSTRAINT [CK_Route_Fare]
CHECK ([Fare] > 0);
ALTER TABLE [Route]
ADD CONSTRAINT [CK_Route_NumberOfPassengersPerDay]
CHECK ([NumberOfPassengersPerDay] > 0);
ALTER TABLE [Route]
ADD CONSTRAINT [CK_Route_NumberOfCarOnRoute]
CHECK ([NumberOfCarOnRoute] >= 0);
DELETE FROM [Route]
DELETE FROM [Way]
DELETE FROM [ModelOfTransport]
DELETE FROM [TypeOfTransport]
DELETE FROM [Driver]
SET IDENTITY_INSERT [Route] ON
INSERT [Route] ([ID], [WayID], [Fare], [NumberOfPassengersPerDay], [NumberOfCarOnRoute]) VALUES
(1, 1, 10, 409, 7)
INSERT [Route] ([ID], [WayID], [Fare], [NumberOfPassengersPerDay], [NumberOfCarOnRoute]) VALUES
(2, 18, 15, 345, 8)
INSERT [Route] ([ID], [WayID], [Fare], [NumberOfPassengersPerDay], [NumberOfCarOnRoute]) VALUES
(3, 20, 17, 149, 6)
INSERT [Route] ([ID], [WayID], [Fare], [NumberOfPassengersPerDay], [NumberOfCarOnRoute]) VALUES
(4, 19, 35, 170, 8)
INSERT [Route] ([ID], [WayID], [Fare], [NumberOfPassengersPerDay], [NumberOfCarOnRoute]) VALUES
(5, 16, 15, 342, 12)
INSERT [Route] ([ID], [WayID], [Fare], [NumberOfPassengersPerDay], [NumberOfCarOnRoute]) VALUES
(6, 17, 25, 350, 11)
INSERT [Route] ([ID], [WayID], [Fare], [NumberOfPassengersPerDay], [NumberOfCarOnRoute]) VALUES
(7, 14, 10, 209, 10)
INSERT [Route] ([ID], [WayID], [Fare], [NumberOfPassengersPerDay], [NumberOfCarOnRoute]) VALUES
(8, 12, 15, 189, 8)
INSERT [Route] ([ID], [WayID], [Fare], [NumberOfPassengersPerDay], [NumberOfCarOnRoute]) VALUES
(9, 11, 20, 190, 4)
INSERT [Route] ([ID], [WayID], [Fare], [NumberOfPassengersPerDay], [NumberOfCarOnRoute]) VALUES
(10, 13, 12, 99, 3)
SET IDENTITY_INSERT [Route] OFF
SET IDENTITY_INSERT [TypeOfTransport] ON
INSERT [TypeOfTransport] ([ID], [RouteID], [AvarageSpeed], [Name], [AmountOfCarInPark], [FuelConsOn100k]) VALUES
(1, 10, 68, 'Trolleybus', 7, 110)
INSERT [TypeOfTransport] ([ID], [RouteID], [AvarageSpeed], [Name], [AmountOfCarInPark], [FuelConsOn100k]) VALUES
(2, 1, 68, 'Train', 6, 109)
INSERT [TypeOfTransport] ([ID], [RouteID], [AvarageSpeed], [Name], [AmountOfCarInPark], [FuelConsOn100k]) VALUES
(3, 2, 68, 'Bus', 5, 100)
INSERT [TypeOfTransport] ([ID], [RouteID], [AvarageSpeed], [Name], [AmountOfCarInPark], [FuelConsOn100k]) VALUES
(4, 3, 68, 'Tram', 2, 99)
INSERT [TypeOfTransport] ([ID], [RouteID], [AvarageSpeed], [Name], [AmountOfCarInPark], [FuelConsOn100k]) VALUES
(5, 4, 68, 'Gazel', 13, 97)
INSERT [TypeOfTransport] ([ID], [RouteID], [AvarageSpeed], [Name], [AmountOfCarInPark], [FuelConsOn100k]) VALUES
(6, 5, 68, 'Train', 27, 90)
INSERT [TypeOfTransport] ([ID], [RouteID], [AvarageSpeed], [Name], [AmountOfCarInPark], [FuelConsOn100k]) VALUES
(7, 6, 68, 'Trolleybus', 57, 90)
INSERT [TypeOfTransport] ([ID], [RouteID], [AvarageSpeed], [Name], [AmountOfCarInPark], [FuelConsOn100k]) VALUES
(8, 7, 68, 'Bus', 5, 60)
INSERT [TypeOfTransport] ([ID], [RouteID], [AvarageSpeed], [Name], [AmountOfCarInPark], [FuelConsOn100k]) VALUES
(9, 8, 68, 'Train', 1, 120)
INSERT [TypeOfTransport] ([ID], [RouteID], [AvarageSpeed], [Name], [AmountOfCarInPark], [FuelConsOn100k]) VALUES
(10, 9, 68, 'Bus', 4, 67)
SET IDENTITY_INSERT [TypeOfTransport] OFF
|