Блог

У меня есть триггер:

 create or replace
TRIGGER JACKET_DELETE 
BEFORE DELETE ON JACKET 
FOR EACH ROW 
BEGIN
  DELETE FROM PORT
  WHERE EXISTS
    (SELECT * FROM port LEFT JOIN device on port.fkdevice = device.pkid
     where port.fkjacket = :old.pkid
     and device.fkdevice_type = 1);

  UPDATE PORT
  set port.fkjacket = null, port.fkport = null
  WHERE EXISTS
(SELECT port.fkjacket, port.fkport FROM port LEFT JOIN device on port.fkdevice = device.pkid
     where port.fkjacket = :old.pkid
     and device.fkdevice_type <> 1);
END;
  

По какой-то причине, когда where в delete совпадениях, удаляется ВСЯ port таблица! Я думал, что мой SQL был правильным, но, очевидно, это не так, и я не вижу, что с ним не так. Кто-нибудь может увидеть проблему, которая заставляет это делать?

Когда update совпадают, все работает так, как ожидалось.

структура таблицы: ссылки на порты для устройства, оболочки и порта

Ваше УДАЛЕНИЕ дважды ссылается на таблицу ПОРТОВ. Для пояснения давайте сначала изменим инструкцию, чтобы включить псевдонимы таблиц:

   DELETE FROM PORT p1
  WHERE EXISTS
    (SELECT * FROM port p2 LEFT JOIN device on p2.fkdevice = device.pkid
     where p2.fkjacket = :old.pkid
     and device.fkdevice_type = 1);
  

Обратите внимание, что подзапрос не связан с p1 . Другими словами, результат этого подзапроса будет идентичным для каждой строки в ПОРТУ, которая рассматривается для удаления. Таким образом, вы либо собираетесь удалить все строки, либо никаких строк.

(Также странно, что вы используете ЛЕВОЕ СОЕДИНЕНИЕ, когда у вас есть предикат, не связанный с объединением, во внешней таблице. Но в худшем случае это проблема эффективности и, скорее всего, просто сбивает с толку любого, кто читает ваш код.)

Я считаю, что то, что вы хотите, это:

 DELETE FROM PORT
WHERE fkjacket = :old.pkid
AND EXISTS
  (SELECT NULL FROM device
   WHERE device.pkid = port.fkdevice
     AND device.fkdevice_type=1);
  

И обновление, похоже, имеет ту же проблему; даже если в настоящее время оно дает ожидаемые результаты, я уверен, что это просто удача из-за данных, с которыми вы тестируете. Я думаю, что это можно упростить до:

 UPDATE PORT
  set port.fkjacket = null, port.fkport = null
  WHERE port.fkjacket = :old.pkid
    AND EXISTS
    (SELECT NULL FROM device
       WHERE port.fkdevice = device.pkid
       AND device.fkdevice_type <> 1);
  

Обратите внимание, что оператору EXISTS все равно, возвращаются ли какие-либо столбцы его подзапросом; просто возвращаются ли строки вообще.

Ваше УДАЛЕНИЕ удаляет все, когда поле fkjacket соответствует :old.pkid, потому что вы не ограничили удаление ни для чего другого. Если предложение EXISTS возвращает строку, то все идет своим чередом.

Измените это на что-то вроде:

   DELETE FROM PORT
  WHERE fkjacket IN
    (SELECT port.fkjacket FROM port LEFT JOIN device on port.fkdevice = device.pkid
     where port.fkjacket = :old.pkid
     and device.fkdevice_type = 1);
  

Это приведет к удалению всех строк в таблице портов, где fkjacket находится в списке значений fkjacket, возвращаемых в списке выбора.

Вы уверены, что ваше обновление работает правильно? Мне кажется, вы должны получать такое же поведение с ним — все обновленные строки.

Редактировать:

Поскольку ваше обновление завершается с ошибкой таким же образом, я предлагаю изменить его на:

   UPDATE PORT
  SET port.fkjacket = null, port.fkport = null
  WHERE fkjacket IN
       (SELECT port.fkjacket 
          FROM port LEFT JOIN device on port.fkdevice = device.pkid
         WHERE port.fkjacket = :old.pkid
           AND device.fkdevice_type <> 1);
  

Это обновит все строки в таблице портов, где fkjacket находится в списке значений fkjacket, возвращаемых в списке выбора.