SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 13.05.2016 08:48:40

Ovenvan
Завсегдатай
Зарегистрирован: 13.05.2016
Сообщений: 27

Триггер, работающий с ошибкой

Добрый день, возникла такая проблема, создал триггер, который при удалении клиента из таблицы клиентов также должен удалить все контракты, с этим клиентом связанные.
Курсор выбирает те записи, контракты которых ещё не истекли.
Поле Individual таблицы insurance_contract содержит id клиентов, являясь внешним ключом.


CREATE TRIGGER DeleteClient BEFORE DELETE ON individual
FOR each ROW
BEGIN
DECLARE id INT;
DECLARE done INT DEFAULT 0;
DECLARE cur1 cursor FOR SELECT Individual FROM insurance_contract WHERE Ending >= now() AND Individual != 0;
OPEN cur1;
repeat
fetch cur1 INTO id;
IF OLD.id_individual = id THEN signal sqlstate '45000' SET message_text = 'Нельзя удалить клиента, срок договор ещё не истёк';
ELSE DELETE FROM insurance_contract WHERE Individual = id;
END IF;
until done END repeat;
close cur1;
END

При попытке удаления клиента из таблицы клиентов выдаёт ошибку: “Error 1329: No data - zero rows fetched, selected, or processed”. Помогите, пожалуйста, разобраться.

Неактивен

 

#2 13.05.2016 09:18:19

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Триггер, работающий с ошибкой

Используйте:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

http://stackoverflow.com/questions/3463 … or-process

Неактивен

 

#3 13.05.2016 09:35:21

Ovenvan
Завсегдатай
Зарегистрирован: 13.05.2016
Сообщений: 27

Re: Триггер, работающий с ошибкой

rgbeast написал:

Используйте:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

http://stackoverflow.com/questions/3463 … or-process

С предыдущей ошибкой справился, спасибо большое.

Но теперь не знаю что сделать с непосредственно удалением, не даёт удалять.

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`insuranceagency`.`insurance_contract`, CONSTRAINT `insurance_contract_ibfk_1` FOREIGN KEY (`Individual`) REFERENCES `individual` (`id_individual`))

Вот так выглядит создание таблиц:

'CREATE TABLE `insurance_contract` (
  `id_contract` int(11) NOT NULL AUTO_INCREMENT,
  `Sum_insured` int(11) NOT NULL,
  `Beginning` date NOT NULL,
  `Ending` date NOT NULL,
  `Individual` int(11) DEFAULT NULL,
  `Company` int(11) DEFAULT NULL,
  `Agent` int(11) NOT NULL,
  `Object` int(11) NOT NULL,
  `Category` int(11) NOT NULL,
  PRIMARY KEY (`id_contract`),
  KEY `Individual_idx` (`Individual`),
  KEY `ID_company_idx` (`Company`),
  KEY `ID_agent_idx` (`Agent`),
  KEY `ID_object_idx` (`Object`),
  KEY `ID_service_idx` (`Category`),
  CONSTRAINT `insurance_contract_ibfk_1` FOREIGN KEY (`Individual`) REFERENCES `individual` (`id_individual`),
  CONSTRAINT `insurance_contract_ibfk_2` FOREIGN KEY (`Company`) REFERENCES `company` (`id_company`),
  CONSTRAINT `insurance_contract_ibfk_3` FOREIGN KEY (`Agent`) REFERENCES `insurance_agent` (`id_agent`),
  CONSTRAINT `insurance_contract_ibfk_4` FOREIGN KEY (`Object`) REFERENCES `object_insurance` (`id_object`),
  CONSTRAINT `insurance_contract_ibfk_5` FOREIGN KEY (`Category`) REFERENCES `guide_services` (`id_guide`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=cp1251'

'CREATE TABLE `individual` (
  `id_individual` int(11) NOT NULL AUTO_INCREMENT,
  `FirstName` varchar(45) NOT NULL,
  `LastName` varchar(45) NOT NULL,
  `Date_of_birth` date NOT NULL,
  `Address` varchar(100) NOT NULL,
  `Passport_id` int(11) NOT NULL,
  `Phone` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`id_individual`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=cp1251'

Неактивен

 

#4 13.05.2016 09:37:45

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Триггер, работающий с ошибкой

Определите FOREIGN KEY с опцией ON DELETE CASCADE и тогда триггер не потребуется

Неактивен

 

#5 13.05.2016 09:40:02

Ovenvan
Завсегдатай
Зарегистрирован: 13.05.2016
Сообщений: 27

Re: Триггер, работающий с ошибкой

rgbeast написал:

Определите FOREIGN KEY с опцией ON DELETE CASCADE и тогда триггер не потребуется

Я бы с радостью, но по заданию требуется реализовать триггер.

Неактивен

 

#6 13.05.2016 09:40:38

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Триггер, работающий с ошибкой

Тогда ON DELETE SET NULL

Неактивен

 

#7 13.05.2016 09:46:50

Ovenvan
Завсегдатай
Зарегистрирован: 13.05.2016
Сообщений: 27

Re: Триггер, работающий с ошибкой

rgbeast написал:

Тогда ON DELETE SET NULL

Опять же благодарение Вам за Вашу помощь, удаление произошло, но только не совсем правильно.

Я даже не знаю, как это описать: при запросе DELETE from individual WHERE id_individual = 11; удалились все записи с клиентами, а из нужной записи только одно поле.

Получается что-то подобное:
http://s8.hostingkartinok.com/uploads/images/2016/05/de1cbb8c8ee60581909f953fb099f82c.png

Отредактированно Ovenvan (13.05.2016 09:55:15)

Неактивен

 

#8 13.05.2016 09:55:18

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Триггер, работающий с ошибкой

У Вас противоречивое задание: использовать триггер для того, для чего уже есть FOREIGN KEY. У FOREIGN KEY три варианта поведения при ON CASCADE:
1. RESTRICT (по-умолчанию). В этом случае удаление не пройдет и до триггера не дойдет дело
2. SET NULL. Удаление сработает, но триггер нет, так как поле id уже будет NULL
3. DELETE - все хорошо, но зачем триггер

Неактивен

 

#9 13.05.2016 10:02:04

Ovenvan
Завсегдатай
Зарегистрирован: 13.05.2016
Сообщений: 27

Re: Триггер, работающий с ошибкой

rgbeast написал:

У Вас противоречивое задание: использовать триггер для того, для чего уже есть FOREIGN KEY. У FOREIGN KEY три варианта поведения при ON CASCADE:
1. RESTRICT (по-умолчанию). В этом случае удаление не пройдет и до триггера не дойдет дело
2. SET NULL. Удаление сработает, но триггер нет, так как поле id уже будет NULL
3. DELETE - все хорошо, но зачем триггер

Я понимаю, просто необходимо реализовать триггер на целостность БД, также перед удалением нужно проверить определённое условие - закончился ли срок контракта или нет.
Вот только алгоритм работает совсем не так, удаляет все записи с клиентами.

Неактивен

 

#10 13.05.2016 10:17:17

Ovenvan
Завсегдатай
Зарегистрирован: 13.05.2016
Сообщений: 27

Re: Триггер, работающий с ошибкой

Немного исправил триггер, и всё заработало

CREATE DEFINER=`root`@`localhost` trigger DeleteClient before delete on individual
for each row
begin
declare id int;
declare done int default 0;
declare cur1 cursor for select Individual from insurance_contract where (Ending >= now() && Individual != 0);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
open cur1;
repeat
fetch cur1 into id;
if old.id_individual = id then signal sqlstate '45000' set message_text = 'Нельзя удалить клиента, срок договор ещё не истёк';
else delete from insurance_contract where Individual = old.id_individual;
end if;
until done end repeat;
close cur1;
end

Отредактированно Ovenvan (13.05.2016 10:35:33)

Неактивен

 

#11 13.05.2016 10:37:02

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Триггер, работающий с ошибкой

delete from insurance_contract where Individual != id; -- удалить все записи из таблицы кроме тех где individual = id

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson