ВМЕСТО триггера УДАЛЕНИЯ для предотвращения удаления при выполнении условия

ProgramBox

ВМЕСТО триггера УДАЛЕНИЯ для предотвращения удаления при выполнении условия

Post author:admin
Запись опубликована:28 августа, 2022
Post category:Вопросы по программированию

#sql #sql-server #triggers #sql-delete

#sql #sql-сервер #триггеры #sql-удалить

Вопрос:

Я пытаюсь настроить триггер ВМЕСТО УДАЛЕНИЯ в таблице с именем Cast в базе данных my Movies. Я хочу, чтобы он предотвращал удаление строки, если количество CastFilmID больше 2

 CREATE TRIGGER Prevent_Cast_Delete ON Cast
INSTEAD OF DELETE 
AS
WHERE COUNT CastFilmID > 2
RAISERROR ('Contains more than 2 cast memebers',16,1)
ROLLBACK TRAN
RETURN;

1. Ваш запрос не совсем имеет смысл. Вы пытаетесь разрешить операторы, которые удаляют не более одного человека из любого данного фильма? Если бы я выполнил оператор, подобный delete from cast where actor in ('A','B','C') удалению актера A и актера B из фильма 1 и актера C из фильма 2, то только удаление фильма 2 (актер C) было бы успешным? или вы имеете в виду, что попытка удалить кого-либо из фильма, в котором уже есть 2 актеров, не увенчается успехом?

2. Второй, поэтому попытка УДАЛИТЬ кого-либо из таблицы актеров, где в фильме 2 или более актеров, не увенчается успехом.

3. Где находится оператор rest SELECT (или UPDATE ) WHERE , которому также принадлежит предложение? И для какой СУБД это нужно?

4. синтаксис с добавлением @stickybit SQLServer

Ответ №1:

 CREATE TRIGGER Prevent_Cast_Delete ON Cast
INSTEAD OF DELETE 
AS 
BEGIN
  DELETE FROM Cast WHERE id IN (
    --Identify only people who work in films with 0 or 1 cast members
    SELECT d.id 
    FROM
      --The list of people we try to delete (request from elsewhere)
      Deleted d 
      LEFT OUTER JOIN 
      --This query counts the cast for every film 
      (SELECT CastFilmID, COUNT(*) as CastCount FROM Cast GROUP BY CastFilmId) c 
      ON
        c.CastFilmID = d.CastFilmID
    --This where clause reduces the list of actor IDs we try to delete, to actually
    --just those actors who are in a film of 1 cast member
    WHERE c.CountCast < 2 OR d.CastFilmID IS NULL
  );
END;

Вы забыли фактически выполнить удаление внутри вашего триггера, поэтому из вашей таблицы никогда ничего не будет удалено.

Я изменил логику: для всех записей, которые вы пытаетесь удалить, могут быть удалены только записи, относящиеся к актеру, который не делится фильмом ни с одним другим актером

Для этого мы берем список людей, которых приложение (например) пыталось удалить — deleted d псевдотаблица, добавляем его в сводку таблицы приведения и выбираем только записи, в которых в сводке указано <2 участников:

 Cast.ID, Cast.CastFilmID
KEANU, MATRIX
LAURE, MATRIX
HUGOW, MATRIX
JARAN, MOBYDI
JESUS, null

Предположим, я говорю delete from cast where id in ('KEANU','JARAN','JESUS') — это пытается удалить людей из каждого фильма, который мы знаем. Он будет реализовывать deleted псевдотаблицу:

 Cast.ID, Cast.CastFilmID
KEANU, MATRIX
JARAN, MOBYDI
JESUS, null

Слева к этому присоединено количество пленок:

 Cast.ID, Cast.CastFilmID, CastCount
KEANU, MATRIX, 3
JARAN, MOBYDI, 1
JESUS, null, null

Предложение WHERE удаляет КИАНУ из результатов, потому что он работает над фильмом с участием 3 актеров (включая его самого)

Следующие 2 записи из исходных запрошенных 3 удаляются ( DELETE FROM cast WHERE ID IN (...this list...) :

 Cast.ID
JARAN
JESUS

Пожалуйста, обратите внимание, что были сделаны следующие предположения о вашей БД:

В таблице приведения есть столбец ID, уникальный для каждого актера-фильма
Идентификатор CastFilmId — это идентификатор из таблицы film, и он повторяется для всех, кто работает над одним и тем же фильмом
Когда актер для фильма не снимается в этом фильме (возможно ли это?) Я предположил, что его CastFilmID равен null. Если this никогда не будет null, в запрос можно было бы внести некоторые упрощения, но в этой форме я предполагал, что записи, в которых актер не назначен фильму, все равно могут быть удалены

Здесь важно понимать, что кто-то, выполняющий запрос на удаление, предоставит вам X количество строк в вашем триггере; это строки, которые человек пытается удалить, но вам нужны дополнительные знания извне этого набора данных (например, «сколько других людей работают над теми же фильмами, что и каждыйэтих людей»), прежде чем вы сможете решить, удалять их или нет. Вот почему мы подсчитываем количество людей на этом фильме, объединяем его с данными удаляемого пользователя, а ЗАТЕМ решаем, следует ли удалять этого пользователя

Действительно, такая магия должна быть выполнена во внешнем интерфейсе. Все становится очень запутанным, когда вы указываете БД что-то удалить, а она этого не делает. В равной степени вы должны помнить, что запросы на УДАЛЕНИЕ могут удалять сотни строк одновременно, поэтому вы не можете использовать упрощенный подход «удаление будет пытаться удалить только одну приведенную строку» — вы должны работать с этими вещами как с наборами данных, присоединяя их к другим наборам данныхи создайте набор данных, которые необходимо удалить