Неправильно работает триггер на удаление и запрет вставки в ms sql management studio

[knn7713]

Здравствуйте!
Есть база данных Городской транспорт(на всякий случай прикладываю запрос на ее создание).
Пусть известна цена за литр топлива. Надо удалить все 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

[Аватар]

1. Вьюха не понятно что вернет, смотри её where
2. Удалять по условию нужно или запросом, или процедурой. Но ни как не триггером, тем более на удаление
3. Запрет на создание скорее всего из-за этого не работает:
INSERTED.ID = TypeOfTransport.RouteID или WHERE INSERTED.Fare < 1.5 * TypeOfTransport.FuelConsOn100k
видимо в TypeOfTransport нет таких строк