Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Неправильно работает триггер  [new]
knn7713
Member

Откуда:
Сообщений: 5
Здравствуйте!
Есть база данных Городской транспорт(на всякий случай прикладываю запрос на ее создание).
Пусть известна цена за литр топлива. Надо удалить все Route(маршруты), у которых цена за проезд(Route.Fare) менее чем в 1.5
превышает расход на топливо(TypeOfTransport.FuelConsOn100k). И не запретить их вставку.
Собственно пишу 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)
);

CREATE TABLE [ModelOfTransport]
(
	[DriverID] [int] NOT NULL,
	[TypeID] [int] NOT NULL,
	[ModelNo] [int] NOT NULL,
	CONSTRAINT [PK_ModelOfTransport] PRIMARY KEY
	(
			[DriverID] ASC,
			[TypeID] ASC,
			[ModelNo] ASC
	)
);

CREATE TABLE [Driver]
(
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [varchar](100) NOT NULL,
	[SecondName] [varchar](100) NOT NULL,
	[Sex] [varchar](100) NOT NULL,
	[BirthDate] [datetime] NOT NULL,
	[PassportNo] [int] NOT NULL,
	[Country] [varchar](100) NOT NULL,
	CONSTRAINT [PK_Driver] PRIMARY KEY ([ID] ASC)
);

CREATE UNIQUE INDEX [IX_Driver] ON [Driver]
(
	[PassportNo] ASC
);

ALTER TABLE [TypeOfTransport]
	ADD CONSTRAINT [FK_TypeOfTransport_Route] FOREIGN KEY([RouteID])
		REFERENCES [Route] ([ID]);

ALTER TABLE [ModelOfTransport]
	ADD CONSTRAINT [FK_ModelOfTransport_TypeOfTransport] FOREIGN KEY([TypeID])
		REFERENCES [TypeOfTransport] ([ID]);

ALTER TABLE [ModelOfTransport]
	ADD CONSTRAINT [FK_ModelOfTransport_Driver] FOREIGN KEY([DriverID])
		REFERENCES [Driver] ([ID]);
		
ALTER TABLE [ModelOfTransport]
	ADD CONSTRAINT [CK_ModelOfTransport_ModelNO]
		CHECK ([ModelNO] > 0);

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);

ALTER TABLE [Way]
	ADD CONSTRAINT [CK_Way_Distance]
		CHECK ([Distance] >= 0);

ALTER TABLE [Way]
	ADD CONSTRAINT [CK_Way_TheNumberOfStops]
		CHECK ([TheNumberOfStops] >= 0);

ALTER TABLE [Way]
	ADD CONSTRAINT [CK_Way_EstimatedTimeEnRoute]
		CHECK ([EstimatedTimeEnRoute] >= 0);

ALTER TABLE [Driver]
	ADD CONSTRAINT [CK_Driver_BirthDate]
		CHECK (YEAR(getdate()) - YEAR([BirthDate]) >= 18);

DELETE FROM [Route]
DELETE FROM [Way]
DELETE FROM [ModelOfTransport]
DELETE FROM [TypeOfTransport]
DELETE FROM [Driver]

SET IDENTITY_INSERT [Driver] ON
INSERT[Driver]([ID], [FirstName], [SecondName], [Sex], [BirthDate], [Country], [PassportNo]) VALUES(1, 'Aaron', 'Adrian', 'M', convert(datetime, '1957-05-17', 102), 'Russia', 100041)
INSERT[Driver]([ID], [FirstName], [SecondName], [Sex], [BirthDate], [Country], [PassportNo]) VALUES(2, 'Herbert', 'Jacobson', 'M', convert(datetime, '1964-07-12', 102), 'Russia', 119169)
INSERT[Driver]([ID], [FirstName], [SecondName], [Sex], [BirthDate], [Country], [PassportNo]) VALUES(3, 'Adam', 'Albertson', 'M', convert(datetime, '1954-06-15', 102), 'Russia', 126962)
INSERT[Driver]([ID], [FirstName], [SecondName], [Sex], [BirthDate], [Country], [PassportNo]) VALUES(4, 'Alice', 'James', 'W', convert(datetime, '1967-12-15', 102), 'Russia', 123281)
INSERT[Driver]([ID], [FirstName], [SecondName], [Sex], [BirthDate], [Country], [PassportNo]) VALUES(5, 'Alan', 'Aldridge', 'M', convert(datetime, '1982-01-11', 102), 'Russia', 102995)
INSERT[Driver]([ID], [FirstName], [SecondName], [Sex], [BirthDate], [Country], [PassportNo]) VALUES(6, 'Jack', 'Jeff', 'M', convert(datetime, '1944-10-17', 102), 'Russia', 132391)
INSERT[Driver]([ID], [FirstName], [SecondName], [Sex], [BirthDate], [Country], [PassportNo]) VALUES(7, 'Albert', 'Allford', 'M', convert(datetime, '1972-09-27', 102), 'Russia', 100292)
INSERT[Driver]([ID], [FirstName], [SecondName], [Sex], [BirthDate], [Country], [PassportNo]) VALUES(8, 'Jake', 'Jenkin', 'M', convert(datetime, '1985-07-23', 102), 'Russia', 119718)
INSERT[Driver]([ID], [FirstName], [SecondName], [Sex], [BirthDate], [Country], [PassportNo]) VALUES(9, 'Alex', 'Alsopp', 'M', convert(datetime, '1978-05-17', 102), 'Russia', 114771)
INSERT[Driver]([ID], [FirstName], [SecondName], [Sex], [BirthDate], [Country], [PassportNo]) VALUES(10, 'Hope', 'Jerome', 'W', convert(datetime, '1989-07-21', 102), 'Russia', 125667)
INSERT[Driver]([ID], [FirstName], [SecondName], [Sex], [BirthDate], [Country], [PassportNo]) VALUES(11, 'Janiffer', 'Anderson', 'W', convert(datetime, '1951-10-20', 102), 'Russia', 128703)
INSERT[Driver]([ID], [FirstName], [SecondName], [Sex], [BirthDate], [Country], [PassportNo]) VALUES(12, 'Jay', 'Johnson', 'M', convert(datetime, '1954-08-27', 102), 'Russia', 117673)
INSERT[Driver]([ID], [FirstName], [SecondName], [Sex], [BirthDate], [Country], [PassportNo]) VALUES(13, 'Alfred', 'Andrews', 'M', convert(datetime, '1958-09-21', 102), 'Russia', 128253)
INSERT[Driver]([ID], [FirstName], [SecondName], [Sex], [BirthDate], [Country], [PassportNo]) VALUES(14, 'Jeff', 'Jones', 'M', convert(datetime, '1947-08-21', 102), 'Russia', 132662)
INSERT[Driver]([ID], [FirstName], [SecondName], [Sex], [BirthDate], [Country], [PassportNo]) VALUES(15, 'Andrew', 'Archibald', 'M', convert(datetime, '1981-11-27', 102), 'Russia', 108723)
INSERT[Driver]([ID], [FirstName], [SecondName], [Sex], [BirthDate], [Country], [PassportNo]) VALUES(16, 'Harper', 'Keat', 'W', convert(datetime, '1975-07-27', 102), 'Russia', 112316)
INSERT[Driver]([ID], [FirstName], [SecondName], [Sex], [BirthDate], [Country], [PassportNo]) VALUES(17, 'Andy', 'Arnold', 'M', convert(datetime, '1946-03-25', 102), 'Russia', 100288)
INSERT[Driver]([ID], [FirstName], [SecondName], [Sex], [BirthDate], [Country], [PassportNo]) VALUES(18, 'Jim', 'Kelly', 'M', convert(datetime, '1988-10-20', 102), 'Russia', 119264)
INSERT[Driver]([ID], [FirstName], [SecondName], [Sex], [BirthDate], [Country], [PassportNo]) VALUES(19, 'Anthony', 'Arthurs', 'M', convert(datetime, '1969-03-22', 102), 'Russia', 115890)
INSERT[Driver]([ID], [FirstName], [SecondName], [Sex], [BirthDate], [Country], [PassportNo]) VALUES(20, 'Ain', 'Rend', 'W', convert(datetime, '1941-09-26', 102), 'Russia', 115006)
SET IDENTITY_INSERT [Driver] OFF

SET IDENTITY_INSERT [Way] ON
INSERT [Way] ([ID], [StartingStation], [EndingStation], [Distance], [TheNumberOfStops], [EstimatedTimeEnRoute]) VALUES
			(1, 'Bank junction','Basinghall Street', 1900, 10, 34)
INSERT [Way] ([ID], [StartingStation], [EndingStation], [Distance], [TheNumberOfStops], [EstimatedTimeEnRoute]) VALUES
			(2, 'Beer Lane', 'Bevis Marks', 4791, 8, 53)
INSERT [Way] ([ID], [StartingStation], [EndingStation], [Distance], [TheNumberOfStops], [EstimatedTimeEnRoute]) VALUES
			(3, 'Bishopsgate', 'Bleeding Heart Yard', 432, 3, 7)
INSERT [Way] ([ID], [StartingStation], [EndingStation], [Distance], [TheNumberOfStops], [EstimatedTimeEnRoute]) VALUES
			(4, 'Bouverie Street', 'Bread Street', 5782, 17, 78)
INSERT [Way] ([ID], [StartingStation], [EndingStation], [Distance], [TheNumberOfStops], [EstimatedTimeEnRoute]) VALUES
			(5, 'Byward Street', 'Camomile Street', 472, 5, 9)
INSERT [Way] ([ID], [StartingStation], [EndingStation], [Distance], [TheNumberOfStops], [EstimatedTimeEnRoute]) VALUES
			(6, 'Cannon Street', 'Chancery Lane', 839, 11, 18)
INSERT [Way] ([ID], [StartingStation], [EndingStation], [Distance], [TheNumberOfStops], [EstimatedTimeEnRoute]) VALUES
			(7, 'Charterhouse Street', 'Cheapside', 4728, 19, 67)
INSERT [Way] ([ID], [StartingStation], [EndingStation], [Distance], [TheNumberOfStops], [EstimatedTimeEnRoute]) VALUES
			(8, 'Chiswell Street', 'City Road', 6754, 29, 89)
INSERT [Way] ([ID], [StartingStation], [EndingStation], [Distance], [TheNumberOfStops], [EstimatedTimeEnRoute]) VALUES
			(9, 'Cloth Fair', 'Cock Lane', 567, 7, 13)
INSERT [Way] ([ID], [StartingStation], [EndingStation], [Distance], [TheNumberOfStops], [EstimatedTimeEnRoute]) VALUES
			(10, 'Coleman Street', 'Cornhill', 1343, 14, 28)
INSERT [Way] ([ID], [StartingStation], [EndingStation], [Distance], [TheNumberOfStops], [EstimatedTimeEnRoute]) VALUES
			(11, 'Cripplegate', 'Crosswall', 4715, 17, 49)
INSERT [Way] ([ID], [StartingStation], [EndingStation], [Distance], [TheNumberOfStops], [EstimatedTimeEnRoute]) VALUES
			(12, 'Giltspur Street', 'Gracechurch Street', 9817, 31, 234)
INSERT [Way] ([ID], [StartingStation], [EndingStation], [Distance], [TheNumberOfStops], [EstimatedTimeEnRoute]) VALUES
			(13, 'Great Tower Street', 'Gresham Street', 3155, 15, 53)
INSERT [Way] ([ID], [StartingStation], [EndingStation], [Distance], [TheNumberOfStops], [EstimatedTimeEnRoute]) VALUES
			(14, 'Gropecunt Lane', 'Grub Street', 5414, 18, 72)
INSERT [Way] ([ID], [StartingStation], [EndingStation], [Distance], [TheNumberOfStops], [EstimatedTimeEnRoute]) VALUES
			(15, 'High Holborn', 'Holborn Viaduct', 346, 5, 19)
INSERT [Way] ([ID], [StartingStation], [EndingStation], [Distance], [TheNumberOfStops], [EstimatedTimeEnRoute]) VALUES
			(16, 'Houndsditch', 'Ironmonger Lane', 4156, 19, 37)
INSERT [Way] ([ID], [StartingStation], [EndingStation], [Distance], [TheNumberOfStops], [EstimatedTimeEnRoute]) VALUES
			(17, 'King Street', 'King William Street', 2160, 21, 20)
INSERT [Way] ([ID], [StartingStation], [EndingStation], [Distance], [TheNumberOfStops], [EstimatedTimeEnRoute]) VALUES
			(18, 'Bench Walk', 'Knightrider Street', 7423, 41, 98)
INSERT [Way] ([ID], [StartingStation], [EndingStation], [Distance], [TheNumberOfStops], [EstimatedTimeEnRoute]) VALUES
			(19, 'Leadenhall Street', 'Lime Street', 3178, 23, 45)
INSERT [Way] ([ID], [StartingStation], [EndingStation], [Distance], [TheNumberOfStops], [EstimatedTimeEnRoute]) VALUES
			(20, 'Little Britain', 'Lombard Street', 5419, 31, 60)
SET IDENTITY_INSERT [Way] OFF

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)
INSERT [Route] ([ID], [WayID], [Fare], [NumberOfPassengersPerDay], [NumberOfCarOnRoute]) VALUES
				(11, 15, 45, 789, 12)
INSERT [Route] ([ID], [WayID], [Fare], [NumberOfPassengersPerDay], [NumberOfCarOnRoute]) VALUES
				(12, 10, 9, 189, 8)
INSERT [Route] ([ID], [WayID], [Fare], [NumberOfPassengersPerDay], [NumberOfCarOnRoute]) VALUES
				(13, 8, 31, 259, 10)
INSERT [Route] ([ID], [WayID], [Fare], [NumberOfPassengersPerDay], [NumberOfCarOnRoute]) VALUES
				(14, 3, 15, 68, 4)
INSERT [Route] ([ID], [WayID], [Fare], [NumberOfPassengersPerDay], [NumberOfCarOnRoute]) VALUES
				(15, 4, 10, 189, 10)
INSERT [Route] ([ID], [WayID], [Fare], [NumberOfPassengersPerDay], [NumberOfCarOnRoute]) VALUES
				(16, 2, 15, 79, 4)
INSERT [Route] ([ID], [WayID], [Fare], [NumberOfPassengersPerDay], [NumberOfCarOnRoute]) VALUES
				(17, 9, 18, 90, 5)
INSERT [Route] ([ID], [WayID], [Fare], [NumberOfPassengersPerDay], [NumberOfCarOnRoute]) VALUES
				(18, 7, 20, 139, 7)
INSERT [Route] ([ID], [WayID], [Fare], [NumberOfPassengersPerDay], [NumberOfCarOnRoute]) VALUES
				(19, 6, 10, 189, 10)
INSERT [Route] ([ID], [WayID], [Fare], [NumberOfPassengersPerDay], [NumberOfCarOnRoute]) VALUES
				(20, 5, 15, 129, 2)
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, 11, 68, 'Train', 6, 109)
INSERT [TypeOfTransport] ([ID], [RouteID], [AvarageSpeed], [Name], [AmountOfCarInPark], [FuelConsOn100k]) VALUES
						 (3, 12, 68, 'Bus', 5, 100)
INSERT [TypeOfTransport] ([ID], [RouteID], [AvarageSpeed], [Name], [AmountOfCarInPark], [FuelConsOn100k]) VALUES
						 (4, 13, 68, 'Tram', 2, 99)
INSERT [TypeOfTransport] ([ID], [RouteID], [AvarageSpeed], [Name], [AmountOfCarInPark], [FuelConsOn100k]) VALUES
						 (5, 14, 68, 'Gazel', 13, 97)
INSERT [TypeOfTransport] ([ID], [RouteID], [AvarageSpeed], [Name], [AmountOfCarInPark], [FuelConsOn100k]) VALUES
						 (6, 15, 68, 'Train', 27, 90)
INSERT [TypeOfTransport] ([ID], [RouteID], [AvarageSpeed], [Name], [AmountOfCarInPark], [FuelConsOn100k]) VALUES
						 (7, 16, 68, 'Trolleybus', 57, 90)
INSERT [TypeOfTransport] ([ID], [RouteID], [AvarageSpeed], [Name], [AmountOfCarInPark], [FuelConsOn100k]) VALUES
						 (8, 17, 68, 'Bus', 5, 60)
INSERT [TypeOfTransport] ([ID], [RouteID], [AvarageSpeed], [Name], [AmountOfCarInPark], [FuelConsOn100k]) VALUES
						 (9, 18, 68, 'Train', 1, 120)
INSERT [TypeOfTransport] ([ID], [RouteID], [AvarageSpeed], [Name], [AmountOfCarInPark], [FuelConsOn100k]) VALUES
						 (10, 19, 68, 'Bus', 4, 67)
INSERT [TypeOfTransport] ([ID], [RouteID], [AvarageSpeed], [Name], [AmountOfCarInPark], [FuelConsOn100k]) VALUES
						 (11, 20, 68, 'Train', 3, 80)
INSERT [TypeOfTransport] ([ID], [RouteID], [AvarageSpeed], [Name], [AmountOfCarInPark], [FuelConsOn100k]) VALUES
						 (12, 1, 68, 'Bus', 12, 79)
INSERT [TypeOfTransport] ([ID], [RouteID], [AvarageSpeed], [Name], [AmountOfCarInPark], [FuelConsOn100k]) VALUES
						 (13, 3, 68, 'Tram', 13, 90)
INSERT [TypeOfTransport] ([ID], [RouteID], [AvarageSpeed], [Name], [AmountOfCarInPark], [FuelConsOn100k]) VALUES
						 (14, 4, 68, 'Trolleybus', 7, 91)
INSERT [TypeOfTransport] ([ID], [RouteID], [AvarageSpeed], [Name], [AmountOfCarInPark], [FuelConsOn100k]) VALUES
						 (15, 5, 201, 'Train', 2, 190)
INSERT [TypeOfTransport] ([ID], [RouteID], [AvarageSpeed], [Name], [AmountOfCarInPark], [FuelConsOn100k]) VALUES
						 (16, 6, 68, 'Gazel', 3, 98)
INSERT [TypeOfTransport] ([ID], [RouteID], [AvarageSpeed], [Name], [AmountOfCarInPark], [FuelConsOn100k]) VALUES
						 (17, 7, 68, 'Tram', 7, 50)
INSERT [TypeOfTransport] ([ID], [RouteID], [AvarageSpeed], [Name], [AmountOfCarInPark], [FuelConsOn100k]) VALUES
						 (18, 8, 68, 'Bus', 27, 96)
INSERT [TypeOfTransport] ([ID], [RouteID], [AvarageSpeed], [Name], [AmountOfCarInPark], [FuelConsOn100k]) VALUES
						 (19, 9, 68, 'Tram',17, 30)
INSERT [TypeOfTransport] ([ID], [RouteID], [AvarageSpeed], [Name], [AmountOfCarInPark], [FuelConsOn100k]) VALUES
						 (20, 2, 200, 'Train',3 , 40)  
SET IDENTITY_INSERT [TypeOfTransport] OFF

INSERT [ModelOfTransport] ([DriverID], [TypeID], [ModelNo]) VALUES
						  (1, 13, 47286)
INSERT [ModelOfTransport] ([DriverID], [TypeID], [ModelNo]) VALUES
						  (2, 12, 47287)
INSERT [ModelOfTransport] ([DriverID], [TypeID], [ModelNo]) VALUES
						  (3, 11, 47288)
INSERT [ModelOfTransport] ([DriverID], [TypeID], [ModelNo]) VALUES
						  (4, 10, 47289)
INSERT [ModelOfTransport] ([DriverID], [TypeID], [ModelNo]) VALUES
						  (5, 9, 47290)
INSERT [ModelOfTransport] ([DriverID], [TypeID], [ModelNo]) VALUES
						  (6, 8, 47291)
INSERT [ModelOfTransport] ([DriverID], [TypeID], [ModelNo]) VALUES
						  (7, 7, 47292)
INSERT [ModelOfTransport] ([DriverID], [TypeID], [ModelNo]) VALUES
						  (8, 6, 47293)
INSERT [ModelOfTransport] ([DriverID], [TypeID], [ModelNo]) VALUES
						  (9, 5, 47294)
INSERT [ModelOfTransport] ([DriverID], [TypeID], [ModelNo]) VALUES
						  (10, 4, 47295)
INSERT [ModelOfTransport] ([DriverID], [TypeID], [ModelNo]) VALUES
						  (11, 3, 47296)
INSERT [ModelOfTransport] ([DriverID], [TypeID], [ModelNo]) VALUES
						  (12, 2, 47297)
INSERT [ModelOfTransport] ([DriverID], [TypeID], [ModelNo]) VALUES
						  (13, 1, 47298)
--*/

Модератор: Портянки кода надо убирать в тег spoiler


Сообщение было отредактировано: 25 май 16, 21:05
25 май 16, 11:28    [19216675]     Ответить | Цитировать Сообщить модератору
 Re: Неправильно работает триггер  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
knn7713
Собственно пишу 2 триггера. На удаления и на вставку
Да у вас триггер Limit ON Route нерабочий
Msg 8101, Level 16, State 1, Procedure Limit, Line 26
An explicit value for the identity column in table 'Route' can only be specified when a column list is used and IDENTITY_INSERT is ON.
25 май 16, 12:00    [19216972]     Ответить | Цитировать Сообщить модератору
 Re: Неправильно работает триггер  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
Вот этот триггер:
DELETE FROM Route
WHERE Route.ID IN
(SELECT ID
FROM r_q)
будет удалять всё из Route, что ссылается на r_q при любом удалении из r_q!!!
Даже если удалять 0 строк.
Это из-за того, что не задействована псевдотаблица deleted
Остальное не смотрел, но это бросается в глаза.
25 май 16, 12:05    [19217015]     Ответить | Цитировать Сообщить модератору
 Re: Неправильно работает триггер  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
knn7713
Однако, при удалении возникает ошибка "Конфликт инструкции DELETE с ограничением REFERENCE "FK_TypeOfTransport_Route". Конфликт произошел в базе данных "Городской транспорт3", таблица "dbo.TypeOfTransport", column 'RouteID'."
А что неправильного?
У вас нет триггера instead of для DELETE, а удаление запрещено, потому что есть записи, ссылающиеся на удаляемую.
knn7713
А триггер на запрещение вставки все равно дает вставить строку
INSERT INTO Route
(ID, WayID, Fare, NumberOfPassengersPerDay, NumberOfCarOnRoute) VALUES
(22, 1, 10, 409, 7)

Ну, потому что не выполняется условие, при котором запрещена вставка.
Достаточно просто вставить отладочный вывод в триггер и посмотреть, что происходит
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
    select 'ROLLBACK'
    ROLLBACK
END
ELSE 
BEGIN 
    select 'INSERT' 
    INSERT INTO Route SELECT * FROM inserted 
END
25 май 16, 12:12    [19217072]     Ответить | Цитировать Сообщить модератору
 Re: Неправильно работает триггер  [new]
knn7713
Member

Откуда:
Сообщений: 5
alexeyvg,
а как написать триггер instead of для DELETE? я потому и пишу сюда, что не смогла его написать сама
и как разрешить удаление записей, ссылающихся на удаляемую?
25 май 16, 20:37    [19220009]     Ответить | Цитировать Сообщить модератору
 Re: Неправильно работает триггер  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
knn7713
как написать триггер instead of для DELETE?
Ну как, примерно как триггер INSTEAD OF INSERT
Или как вы для VIEW написали, только, конечно, нужно джойнить с таблицей deleted
CREATE TRIGGER tD_Route ON Route
INSTEAD OF DELETE AS
BEGIN 
    DELETE r
    FROM Route r
        JOIN deleted d
        ON d.id = r.id
END

knn7713
как разрешить удаление записей, ссылающихся на удаляемую?
Так их никто не запрещает удалять, вы это просто не делаете.

Удалите эти записи, тогда ничего не будет мешать удалить основные записи, поскольку на них никто не будет ссылаться.
25 май 16, 21:37    [19220268]     Ответить | Цитировать Сообщить модератору
 Re: Неправильно работает триггер  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Если логики нет, то можно решить через ON CASCADE DELETE
25 май 16, 21:59    [19220347]     Ответить | Цитировать Сообщить модератору
 Re: Неправильно работает триггер  [new]
knn7713
Member

Откуда:
Сообщений: 5
alexeyvg,
пытаюсь запустить триггер
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
    select 'ROLLBACK'
    ROLLBACK
END
ELSE 
BEGIN 
    select 'INSERT' 
	SET IDENTITY_INSERT Route ON
    INSERT INTO Route
	SELECT inserted.ID, inserted.WayID, inserted.Fare, inserted.NumberOfPassengersPerDay, inserted.NumberOfCarOnRoute
	FROM inserted 
	SET IDENTITY_INSERT Route OFF
END

но вылетает ошибка
Значение столбца идентификаторов в таблице "Route" может указываться явно только при использовании списка столбцов и когда IDENTITY_INSERT установлен в ON.
пыталась уже вместо Route написать его столбцы, но так тоже не работает
как это исправить?

Сообщение было отредактировано: 26 май 16, 00:32
25 май 16, 22:04    [19220365]     Ответить | Цитировать Сообщить модератору
 Re: Неправильно работает триггер  [new]
iljy
Member

Откуда:
Сообщений: 8711
knn7713
пыталась уже вместо Route написать его столбцы, но так тоже не работает
как это исправить?


Писать надо не вместо, а вместе
25 май 16, 22:26    [19220443]     Ответить | Цитировать Сообщить модератору
 Re: Неправильно работает триггер  [new]
knn7713
Member

Откуда:
Сообщений: 5
iljy,
так, хорошо, это уже компилится
но у меня никогда запрос не попадает в ветку "rollback", какую бы вставку я не делала
может кто-нибудь объяснить, почему такая выборка никогда не существует? и как это можно исправить..
SELECT INSERTED.ID
	FROM INSERTED JOIN TypeOfTransport ON INSERTED.ID = TypeOfTransport.RouteID
	WHERE INSERTED.Fare < 1.5 * TypeOfTransport.FuelConsOn100k
25 май 16, 22:53    [19220541]     Ответить | Цитировать Сообщить модератору
 Re: Неправильно работает триггер  [new]
iljy
Member

Откуда:
Сообщений: 8711
knn7713,

так вы новый маршрут вставляете, ясный пень на него еще не может быть ссылок по FK
25 май 16, 22:59    [19220553]     Ответить | Цитировать Сообщить модератору
 Re: Неправильно работает триггер  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
Непонятно, зачем ROLLBACK, если триггер INSTEAD OF и ещё не было ни DELETE, ни INSERT, ни UPDATE.
Хотите в триггере беспричинно откатить транзакцию, стартовавшую снаружи? А хорошо ли это?
26 май 16, 10:19    [19221561]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить