如何解决有关Oracle触发器的练习 [英] How to solve this exercise about Oracle Triggers

问题描述

我必须解决有关触发器的练习:

I have to solve this exercise about triggers:

考虑以下用于表示的关系数据库架构项目信息:

Consider the following relational database schema used to represent project information:

人员(ID，姓氏，姓名，国籍)

Person (ID, Surname, Name, Nationality)

项目(名称，经理，从年初开始，参与NumPeople，国际)

Project (Name, Manager, StartingYear, NumPeopleInvolved, International)

人员(项目，PersonID)

Personnel (Project, PersonID)

指定Oracle中维护以下各项所需的触发器完整性约束:

Specify the triggers required in Oracle to maintain the following integrity constraints:

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

a) The number of people involved in a project (attribute NumPeopleInvolved) must be consistent with the number of tuples entered in Personnel for that project

b)如果项目是国际项目(国际"属性假设只有两个值)，那么该项目必须至少包含两个不同民族的人

b) If the project is international (the International attribute assumes only two values) then the project must involve at least two people of different nationalities

我对b)部分有疑问.

我不知道如何处理给定项目没有人员参与的情况.如果我尝试插入第一个人，那么我就不会有两个不同国籍的人，因为我只有一个人.

I don't know how to handle the case in which a given Project has no people involved. If I try to insert the first people, I can not have two people of different nationalities since I have only one people.

应如何处理这种情况?

我应该使用语句级触发器吗?我没有触发器的经验，所以我仍然不太了解我能/不能使用一种触发器.

Should I use a statement level trigger? I have not experience with triggers so I still haven't understood well what I can / I can't do with one kind of trigger.

我尝试过这种方式，但显然无法正常工作

I tried this way but it's clearly not working as it should:

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表的插入，删除和更新.它只是检查并更新每个项目的国际一致性，是否更改了人员表.

I think the following should work with insertions, deletions and updates on table Personnel. It simply check and update the international consistency for each project whether the table Personnel is altered.

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;

这篇关于如何解决有关Oracle触发器的练习的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！