Cómo resolver este ejercicio sobre Oracle Triggers

Question

Tengo que resolver este ejercicio sobre desencadenantes:

Considere el siguiente esquema de base de datos relacional que se utiliza para representar la información del proyecto:

Persona (DNI, Apellido, Nombre, Nacionalidad)

Proyecto (nombre, gerente, año inicial, número de personas involucradas, internacional)

Personal (Proyecto, PersonID)

Especifique los activadores necesarios en Oracle para mantener las siguientes restricciones de integridad:

a) El número de personas involucradas en un proyecto (atributo NumPeopleInvolved) debe ser consistente con el número de tuplas ingresadas en Personal para ese proyecto

b) Si el proyecto es internacional (el atributo Internacional asume solo dos valores) entonces el proyecto debe involucrar al menos a dos personas de diferentes nacionalidades

Tengo un problema con la parte b).

No sé cómo manejar el caso en el que un Proyecto dado no tiene personas involucradas. Si trato de insertar las primeras personas, no puedo tener dos personas de distintas nacionalidades ya que solo tengo una.

¿Cómo se debe manejar esta situación?

¿Debería usar un disparador a nivel de declaración? No tengo experiencia con los desencadenantes, por lo que todavía no he entendido bien lo que puedo / no puedo hacer con un tipo de desencadenante.

Intenté de esta manera, pero claramente no funciona como debería:

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

La mejor forma de hacerlo es con un disparador compuesto. Con un disparador compuesto evitamos el problema de la mutación de tablas que obtendríamos de un disparador de nivel de fila en PERSONNEL.

Realizamos un seguimiento de cada proyecto al que hace referencia cada fila afectada en una declaración DML (insertar, actualizar, eliminar) en una matriz. Al final de la declaración, consultamos esos proyectos para saber si el proyecto es internacional y si es para verificar las nacionalidades de su personal asignado.

Podría verse así:

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;

Aquí hay una demostración funcional en db <> fiddle . Puedes ver que aunque el disparador permite que un proyecto internacional tenga solo una persona asignada arroja un error cuando agregamos una segunda persona de la misma nacionalidad. Podemos resolver esto insertando filas en un orden especial, o mejor insertando un conjunto de filas. Este es un problema con la aplicación de tales reglas comerciales.

Puede usar el mismo enfoque (en el mismo activador) para verificar si el número de personal asignado cumple con la Project.NumPeopleInvolvedregla.

---

Nota: los disparadores compuestos llegaron a Oracle 11gR1.

Answer 2

Creo que lo siguiente debería funcionar con inserciones, eliminaciones y actualizaciones en la tabla Personal. Simplemente verifica y actualiza la consistencia internacional para cada proyecto si la tabla Personal está 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

Cuando tiene un disparador de nivel de fila en la tabla Personnel, no puede ejecutar ningún SELECT en la tabla Personneldentro del disparador; obtendrá un ORA-04091: table PERSONEL is mutating ...error.

Creo que tu profesor espera algo como esto:

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;

En realidad, no implementaría tal requisito con un disparador, usaría un procedimiento PL / SQL.

El atributo NumPeopleInvolvedes inútil, es decir, redundante. Normalmente lo resolvería

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

Esta actualización podría realizarse mediante un disparador, por ejemplo.

En realidad, también necesitaría desencadenantes similares sobre la mesa Personnely Person, debido a que el personal o las personas pueden cambiar y el proyecto se volvería inconsistente. No sé si esto debería ser considerado por el ejercicio.

Imagínese, una persona se libera, es decir, se elimina de la tabla Persona:

• ¿La aplicación generaría un error: la persona no puede ser liberada (qué sucede si la persona muere por Corona :-))?
• ¿El proyecto no sería válido?
• ¿Se actualizaría automáticamente el proyecto?

Entonces, nunca debe generar errores como raise_application_error(-1): ¡siempre informe al usuario qué salió mal!