Блог

Я должен решить это упражнение о триггерах:

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

Человек (идентификатор, фамилия, имя, национальность)

Проект (название, менеджер, начальный год, количество участников, международный)

Персонал (Project, PersonID)

Укажите триггеры, необходимые в Oracle для поддержания следующих ограничений целостности:

a) Количество людей, участвующих в проекте (атрибут NumPeopleInvolved), должно соответствовать количеству кортежей, введенных в персонал для этого проекта

б) Если проект международный (международный атрибут принимает только два значения), то в проекте должны участвовать как минимум два человека разных национальностей

У меня проблема с частью b).

Я не знаю, как справиться со случаем, когда в данном проекте не задействованы люди. Если я попытаюсь вставить первых людей, у меня не может быть двух людей разных национальностей, поскольку у меня есть только один человек.

Как следует обращаться с этой ситуацией?

Должен ли я использовать триггер на уровне оператора? У меня нет опыта работы с триггерами, поэтому я до сих пор не совсем понял, что я могу / не могу сделать с одним типом триггера.

Я пробовал этот способ, но он явно работает не так, как должен:

 CREATE TRIGGER InsertPersonnelInternational
AFTER INSERT ON Personnel
FOR EACH ROW
BEGIN
    SELECT ProjectName
    FROM Personnel INNER JOIN Project
    WHERE PersonID = :new.ID Project = Name

    SELECT International
    FROM Personnel INNER JOIN Project
      ON Project = Name

    SELECT COUNT(*) AS NumPersonnel
    FROM Personnel
    WHERE Project = :new.Project

    IF NumPersonnel >= 1 THEN
    BEGIN
        SELECT COUNT(*) AS NumNationalities
        FROM Personnel INNER JOIN Person
        ON Project = ProjectName
        GROUP BY Nationality

        IF International THEN
            IF NumNationalities = 1 Then
            BEGIN
                raise_application_error(-1)
            END
        ELSE
            IF NumNationalities <> 1 THEN
            BEGIN
                raise_application_error(-1)
            END
        END
    END
END
  

Когда у вас есть триггер на уровне строки в таблице Personnel , вы не можете выполнить какой-либо выбор в таблице Personnel внутри триггера — вы получите ORA-04091: table PERSONEL is mutating ... сообщение об ошибке.

Я думаю, ваш учитель ожидает чего-то подобного:

 CREATE TRIGGER ProjectConsistency
    BEFORE INSERT OR UPDATE ON PROJECT
    FOR EACH ROW
    
    p_count INTEGER;
    n_count INTEGER;

BEGIN

    SELECT COUNT(*)
    INTO p_count
    FROM Personnel
    WHERE PROJECT = :new.NAME;
        
    IF :new.NumPeopleInvolved <> p_count THEN
        RAISE_APPLICATION_ERROR(-20010, 'The number of people involved in a project must be consistent with the number of tuples entered in Personnel for that project');
    END IF;

    IF :new.International = 'YES' THEN
        SELECT COUNT(DISTINCT Nationality)
        INTO n_count
        FROM Personnel
        WHERE PROJECT = :new.NAME;
        
        IF n_count < 2 THEN
            RAISE_APPLICATION_ERROR(-20010, 'The project must involve at least two people of different nationalities')
        END IF;    
    END IF;

END;
  

На самом деле вы бы не реализовали такое требование с помощью триггера, вы бы использовали процедуру PL / SQL.

Атрибут NumPeopleInvolved бесполезен, т. Е. Избыточен. Обычно вы решаете это с помощью

 UPDATE PROJECT proj 
SET NumPeopleInvolved = 
    (SELECT COUNT(*)
    FROM Personnel p
    WHERE PROJECT = :new.NAME)
WHERE NAME = :new.NAME;
  

Например, такое обновление может быть выполнено с помощью триггера.

На самом деле вам понадобятся аналогичные триггеры также для таблицы Personnel и Person , поскольку personel / persons могут измениться, и проект станет непоследовательным. Я не знаю, следует ли это учитывать в упражнении.

Представьте, что on person освобождается, т. Е. Удаляется из таблицы Person:

• вызовет ли приложение сообщение об ошибке — человек не может быть освобожден (что произойдет, если человек умрет от короны :-))?
• будет ли проект недействительным?
• будет ли проект автоматически обновляться?

Затем вы никогда не должны вызывать такие ошибки, как raise_application_error(-1) — всегда сообщайте пользователю, что пошло не так!

Лучший способ сделать это — с помощью составного триггера. С помощью составного триггера мы избегаем проблемы изменения таблиц, которые мы получили бы от триггера уровня строк для ПЕРСОНАЛА.

Мы отслеживаем каждый проект, на который ссылается каждая затронутая строка в инструкции DML (insert, update, delete) в массиве. В конце инструкции мы запрашиваем эти проекты, чтобы выяснить, является ли проект международным, и если это так, чтобы проверить национальность назначенного ему персонала.

Это может выглядеть так:

 CREATE OR REPLACE TRIGGER international_project_trg
  FOR insert or update or delete ON personnel
    COMPOUND TRIGGER

  -- Global declaration
  type project_t is table of number index by personnel.project%type;
  g_project project_t; 

  BEFORE EACH ROW IS
  BEGIN
    CASE
      -- we don't care about the value here, we just what a set of distinct projects
      WHEN INSERTING THEN
        g_project(:new.project) := 1;
      WHEN UPDATING THEN
        g_project(:new.project) := 1;
      WHEN DELETING THEN
        g_project(:old.project) := 1;
    END CASE;
  END BEFORE EACH ROW;

  AFTER STATEMENT IS
    l_project personnel.project%type;
    l_country_cnt pls_integer;
    l_people_cnt pls_integer; 
  BEGIN
    l_project := g_project.first();
    
    while l_project is not null loop
      select count(distinct ppl.nationality)
             ,count(*) 
       into l_country_cnt
            ,l_people_cnt
       from personnel per
            join project prj on per.project  = prj.name
            join person  ppl on per.personid = ppl.id     
        where per.project = l_project
        and   prj.international = 'Y';
        
        if l_people_cnt <= 1 then
          -- either not international project or only one assigned person
          -- so we don't care
          null;
        elsif l_country_cnt <= 1 then
          raise_application_error(-20999, l_project ||' must have multi-national team membership');  
        end if;
        
        l_project := g_project.next(l_project);
        
    end loop;    
    
  END AFTER STATEMENT;

END international_project_trg;
  

Вот рабочая демонстрация на db<>fiddle. Вы можете видеть, что, хотя триггер позволяет международному проекту иметь только одного назначенного сотрудника, он выдает ошибку, когда мы добавляем второго человека той же национальности. Мы можем решить эту проблему, вставив строки в специальном порядке или, что лучше, вставив набор строк. Это проблема с применением таких бизнес-правил.

Вы можете использовать тот же подход (в том же триггере), чтобы проверить, соответствует ли количество назначенного персонала Project.NumPeopleInvolved правилу.

Примечание: составные триггеры поступили в Oracle 11gR1.

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

 CREATE TRIGGER UpdateInternationalProject
AFTER INSERT OR UPDATE OR DELETE ON Personnel
BEGIN
    SELECT name, international
    FROM Project
    AS ProjectInternational;

    FOR projectInfo IN ProjectInternational
    LOOP
        SELECT COUNT(DISTINCT nationality)
            AS numNationalities
        FROM Personnel INNER JOIN Person
        ON personId = id
        WHERE project = projectInfo.name;

        IF numNationalities = 1 THEN
            IF projectInfo.international THEN
                UPDATE Project
                SET international = 0
                WHERE name = projectInfo.name;
            END IF;
        ELIF numNationalities > 1 THEN
            IF NOT projectInfo.international THEN
                UPDATE Project
                SET international = 1
                WHERE name = projectInfo.name;
            END IF;
        END IF;
    END LOOP;
END;