如何完成有关 Oracle 触发器的练习

我必须解决这个关于触发器的练习：

考虑以下用于表示的关系数据库模式 项目信息：

人员（身份证、姓氏、姓名、国籍）

项目（姓名、经理、 起始年份，参与人数，国际）

人员（项目、人员 ID）

指定Oracle中需要的触发器来维护以下内容 完整性约束：

a) 参与项目的人数（属性 NumPeopleInvolved) 必须与元组数量一致 输入该项目的人员

b) 如果项目是国际性的（国际属性 仅假设两个值）那么该项目必须至少涉及两个 不同国籍的人

我对 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那么你不能在表上运行任何 SELECTPersonnel在触发器内 - 你会得到一个ORA-04091: table PERSONEL is mutating ... error.

我想你的老师期待的是这样的事情：

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 and Person，因为人员可能会发生变化，项目会变得不一致。我不知道练习是否应该考虑这一点。

想象一下，一个人被释放，即从表 Person 中删除：

• 应用程序会引发错误 - 人员无法被释放（如果人员因新冠死亡会发生什么:-)）？
• 项目会无效吗？
• 项目会自动更新吗？

那么，你应该never引发错误，例如raise_application_error(-1)- 始终让用户知道出了什么问题！