sql - 触发器行为异常

Question

我正在制作一个数据库，该数据库需要为学校系统存储数据，并且对于某些未成年人需要许可的事件。我有这个触发器来检查如果一个参与者是未成年人，那么他需要许可证。

CREATE OR REPLACE FUNCTION absent_permit()
RETURNS trigger AS $body$
BEGIN
     RAISE NOTICE 'ENTERS TRIGGER';
     RAISE NOTICE 'AGE NEW PARTICIPANT = %', age_from_participant(NEW.id_part);
     IF (age_from_participant(NEW.id_part) < 18) AND (NEW.permit IS NULL) THEN
          RAISE NOTICE 'ENTERS IF';
          RAISE NOTICE 'PARTICIPANT UNDER 18 NEED A PERMIT';
          RAISE EXCEPTION 'MINOR WITHOUT PERMIT' USING HINT = 'PERMIT MUST BE != NULL';
          RETURN NULL;
     END IF;
     RAISE NOTICE 'EXIT TRIGGER';
     RETURN NEW;
END
$body$
LANGUAGE plpgsql;


CREATE TRIGGER check_permit BEFORE INSERT ON participant
FOR EACH ROW EXECUTE PROCEDURE absent_permit();

事实是:我第一次尝试在未经许可的情况下插入一个未成年人它不起作用并无论如何插入它，第二次我尝试而不是由于重复的主键而给我一个错误，触发器工作并引发它应该第一次提出的异常(exception)。 age_from_participant 函数肯定以正确的方式工作，这不是问题所在。

有人可以告诉我我做错了什么吗？谢谢

编辑:添加表人、参与者和插入示例

CREATE TABLE people (
    fiscal_code varchar(16) PRIMARY KEY,
    name        varchar(20) NOT NULL,
        surname         varchar(20) NOT NULL,
        phone       numeric(10, 0)  NOT NULL,
        sex             char(1)     NOT NULL    CHECK(sex IN ('M','F')),
        email       varchar(50) NOT NULL,
    d_birth     date            NOT NULL
);

CREATE TABLE participant (
    id_part         varchar(10) PRIMARY KEY,
    fiscal_code     varchar(16) NOT NULL    REFERENCES people(fiscal_code),
        documents_code      varchar(1000)   NOT NULL,
        section         varchar(2)  NOT NULL,
        grade           varchar(2)  NOT NULL,
        permit          varchar(500),
        school_code     varchar(10) NOT NULL    REFERENCES schools(school_code)
);

CREATE OR REPLACE FUNCTION age_from_participant(varchar)
RETURNS integer AS $$
DECLARE
     alias_id_part ALIAS FOR $1;
     precise_age interval;
BEGIN
    SELECT AGE(CURRENT_DATE, d_birth) INTO precise_age FROM participant
        JOIN PEOPLE USING(fiscal_code)
        WHERE PARTICIPANT.ID_PART = alias_id_part;
     RETURN date_part('year', precise_age)::INT;
END $$
LANGUAGE plpgsql;


INSERT INTO people VALUES ('RSSMRC0123456789', 'MARCO', 'ROSSI', 3490101123, 'M', 'MARCOROSSI@GMAIL.COM', '2000-07-18');

INSERT INTO participant VALUES ('PART000001', 'RSSMRC0123456789','MR001','A','4',null, 'GEPC01000P');

最后一次插入是第一次正确插入，控制台给出:

NOTICE:  ENTERS TRIGGER
NOTICE:  AGE NEW PARTICIPANT = 
NOTICE:  EXIT TRIGGER
INSERT 0 1
Query returned successfully.

第二次给出:

ERROR:  MINOR WITHOUT PERMIT
HINT:  PERMIT MUST BE != NULL
CONTEXT:  PL/pgSQL function absent_permit() line 8 at RAISE
********** Error **********

ERROR: MINOR WITHOUT PERMIT
SQL state: P0001
Hint: PERMIT MUST BE != NULL
Context: PL/pgSQL function absent_permit() line 8 at RAISE

编辑_2:我解决了将 age_from_partecipant 的代码带入触发函数的问题。仍然不知道到底是什么不起作用，但这里是工作代码:

CREATE OR REPLACE FUNCTION absent_permit()
RETURNS trigger AS $body$
DECLARE
     age  interval;
BEGIN
SELECT AGE(CURRENT_DATE, D_birth) INTO age FROM participant JOIN people USING(fiscal_code) WHERE NEW.fiscal_code = people.fiscal_code;

     RAISE NOTICE 'ENTERS TRIGGER';
     RAISE NOTICE 'AGE NEW PARTICIPANT = %', date_part('year', age)::INT;
     IF (date_part('year', age)::INT < 18) AND (NEW.permit IS NULL) THEN
          RAISE NOTICE 'ENTERS IF';
          RAISE NOTICE 'PARTICIPANT UNDER 18 NEED A PERMIT';
          RAISE EXCEPTION 'MINOR WITHOUT PERMIT' USING HINT = 'PERMIT MUST BE != NULL';
          RETURN NULL;
     END IF;
     RAISE NOTICE 'EXIT TRIGGER';
     RETURN NEW;
END
$body$
LANGUAGE plpgsql;


CREATE TRIGGER check_permit BEFORE INSERT ON participant
FOR EACH ROW EXECUTE PROCEDURE absent_permit();

Answer 1

我不认为 età_from_participant() 可以在 participant 中找到参与者——因为交易还没有完成。也就是说，您有一个之前 插入触发器。该行不在表中。

我的建议是将该逻辑直接移动到触发器中。或者，也可以创建 età_from_person() 并将财务代码传递到该函数中。