Come risolvere questo esercizio sui trigger Oracle

Question

Devo risolvere questo esercizio sui trigger:

Considera il seguente schema di database relazionale utilizzato per rappresentare le informazioni sul progetto:

Persona (ID, Cognome, Nome, Nazionalità)

Progetto (nome, manager, anno di inizio, numero di persone coinvolte, internazionale)

Personale (progetto, ID personale)

Specificare i trigger richiesti in Oracle per mantenere i seguenti vincoli di integrità:

a) Il numero di persone coinvolte in un progetto (attributo NumPeopleInvolved) deve essere coerente con il numero di tuple inserite in Personale per quel progetto

b) Se il progetto è internazionale (l'attributo Internazionale assume solo due valori) allora il progetto deve coinvolgere almeno due persone di nazionalità diverse

Ho un problema con la parte b).

Non so come gestire il caso in cui un determinato progetto non ha persone coinvolte. Se provo a inserire le prime persone, non posso avere due persone di nazionalità diversa poiché ho una sola persona.

Come dovrebbe essere gestita questa situazione?

Devo usare un trigger a livello di istruzione? Non ho esperienza con i trigger, quindi non ho ancora capito bene cosa posso / non posso fare con un tipo di trigger.

Ho provato in questo modo ma chiaramente non funziona come dovrebbe:

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

Il modo migliore per farlo è con un trigger composto. Con un trigger composto evitiamo il problema di modificare le tabelle che otterremmo da un trigger a livello di riga su PERSONNEL.

Teniamo traccia di ogni progetto a cui fa riferimento ogni riga interessata in un'istruzione DML (insert, update, delete) in un array. Alla fine della dichiarazione interroghiamo quei progetti per scoprire se il progetto è internazionale e se deve controllare le nazionalità del personale assegnato.

Potrebbe assomigliare a questo:

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;

Ecco una demo funzionante su db <> fiddle . Puoi vedere che sebbene il trigger consenta a un progetto internazionale di avere una sola persona assegnata, genera un errore quando aggiungiamo una seconda persona della stessa nazionalità. Possiamo risolvere questo problema inserendo righe in un ordine speciale, o meglio inserendo un insieme di righe. Questo è un problema con l'applicazione di tali regole aziendali.

È possibile utilizzare lo stesso approccio (nello stesso trigger) per verificare se il numero di personale assegnato soddisfa la Project.NumPeopleInvolvedregola.

---

Nota: i trigger composti sono arrivati ​​in Oracle 11gR1.

Answer 2

Penso che quanto segue dovrebbe funzionare con inserimenti, eliminazioni e aggiornamenti sulla tabella Personale. Controlla e aggiorna semplicemente la coerenza internazionale per ogni progetto se la tabella Personale è alterata.

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 si dispone di un trigger a livello di riga sulla tabella, Personnelnon è possibile eseguire alcun SELECT sulla tabella Personnelall'interno del trigger: verrà visualizzato un ORA-04091: table PERSONEL is mutating ...errore.

Penso che il tuo insegnante si aspetti qualcosa del genere:

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;

In realtà non implementereste tale requisito con un trigger, usereste una procedura PL / SQL.

L'attributo NumPeopleInvolvedè inutile, cioè ridondante. In genere lo risolveresti con

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

Un tale aggiornamento potrebbe essere eseguito da un trigger, ad esempio.

In realtà avresti bisogno di trigger simili anche sul tavolo Personnele Person, perché il personale / le persone potrebbero cambiare e il progetto diventerebbe incoerente. Non so se questo debba essere considerato dall'esercizio.

Immagina, su person viene rilasciato, ovvero eliminato dalla tabella Person:

• l'applicazione genererebbe un errore - la persona non può essere rilasciata (cosa succede se la persona muore per Corona :-))?
• il progetto non sarebbe valido?
• il progetto verrebbe aggiornato automaticamente?

Quindi, non dovresti mai generare errori come raise_application_error(-1): fai sempre sapere all'utente cosa è andato storto!