Como resolver este exercício sobre Oracle Triggers

Question

Tenho que resolver este exercício sobre gatilhos:

Considere o seguinte esquema de banco de dados relacional usado para representar as informações do projeto:

Pessoa (RG, Sobrenome, Nome, Nacionalidade)

Projeto (Nome, Gerente, StartingYear, NumPeopleInvolved, International)

Pessoal (Projeto, Identificação da Pessoa)

Especifique os gatilhos necessários no Oracle para manter as seguintes restrições de integridade:

a) O número de pessoas envolvidas em um projeto (atributo NumPeopleInvolved) deve ser consistente com o número de tuplas inseridas em Pessoal para esse projeto

b) Se o projeto for internacional (o atributo Internacional assume apenas dois valores), o projeto deve envolver pelo menos duas pessoas de nacionalidades diferentes

Tenho um problema com a parte b).

Não sei como lidar com o caso de um determinado Projeto não ter pessoas envolvidas. Se tento inserir as primeiras pessoas, não posso ter duas pessoas de nacionalidades diferentes, pois tenho apenas uma pessoa.

Como essa situação deve ser tratada?

Devo usar um gatilho de nível de instrução? Não tenho experiência com gatilhos, então ainda não entendi bem o que posso / não posso fazer com um tipo de gatilho.

Eu tentei dessa forma, mas claramente não está funcionando como deveria:

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

Answer 1

A melhor maneira de fazer isso é com um gatilho composto. Com um gatilho composto, evitamos o problema de transformar tabelas que obteríamos de um gatilho de nível de linha em PERSONNEL.

Acompanhamos cada projeto que é referenciado por cada linha afetada em uma instrução DML (inserir, atualizar, excluir) em uma matriz. No final da declaração, consultamos esses projetos para saber se o projeto é internacional e se é para verificar as nacionalidades do seu pessoal designado.

Pode ser assim:

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;

Aqui está uma demonstração de trabalho no db <> fiddle . Você pode ver que, embora o gatilho permita que um projeto internacional tenha apenas uma pessoa atribuída, ele gera um erro quando adicionamos uma segunda pessoa da mesma nacionalidade. Podemos resolver isso inserindo linhas em uma ordem especial ou, melhor, inserindo um conjunto de linhas. Este é um problema com a aplicação de tais regras de negócios.

Você pode usar a mesma abordagem (no mesmo gatilho) para verificar se o número de funcionários atribuídos atende à Project.NumPeopleInvolvedregra.

---

Nota: gatilhos compostos chegaram ao Oracle 11gR1.

Answer 2

Acho que o seguinte deve funcionar com inserções, exclusões e atualizações na tabela Pessoal. Simplesmente verifica e atualiza a consistência internacional para cada projeto se a tabela Pessoal é alterada.

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;

Answer 3

Quando você tem um gatilho de nível de linha na tabela Personnel, não pode executar nenhum SELECT na tabela Personneldentro do gatilho - você obterá um ORA-04091: table PERSONEL is mutating ...erro.

Acho que seu professor está esperando algo assim:

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;

Na realidade, você não implementaria tal requisito com um gatilho, mas usaria um procedimento PL / SQL.

O atributo NumPeopleInvolvedé inútil, ou seja, redundante. Normalmente você resolveria por

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

Essa atualização pode ser feita por um gatilho, por exemplo.

Na verdade, você precisaria de gatilhos semelhantes também na mesa Personnele Person, porque o pessoal / pessoas podem mudar e o projeto se tornaria inconsistente. Não sei se isso deve ser considerado pelo exercício.

Imagine, uma pessoa é liberada, ou seja, excluída da tabela Pessoa:

• o aplicativo levantaria um erro - a pessoa não pode ser liberada (o que acontece se a pessoa morrer por Corona :-))?
• seria o projeto inválido?
• o projeto seria atualizado automaticamente?

Então, você nunca deve levantar erros como raise_application_error(-1)- sempre deixe o usuário saber o que deu errado!