mysql - 触发以防止同时类型

Question

我想编写一个触发器，以确保我的数据库中的诊所接待员或护士不能同时成为医生。请注意，所有这些不同的职业都是具有相同外键的不同表(它们都是雇员)。代码应该更清楚:

create table employee
(
    employee_VAT varchar(255),
    employee_name varchar(255),
    employee_birth_date date,
    employee_street varchar(255),
    employee_city varchar(255),
    employee_ZIP varchar(255),
    employee_IBAN varchar(255),
    employee_salary numeric(20,2),
    primary key(employee_VAT),
    unique(employee_IBAN)
);

create table nurse
(
    employee_VAT varchar(255),
    primary key(employee_VAT),
    foreign key(employee_VAT) references employee(employee_VAT)
);

create table receptionist
(
    employee_VAT varchar(255),
    primary key(employee_VAT),
    foreign key(employee_VAT) references employee(employee_VAT)
);


create table doctor
(
    employee_VAT varchar(255),
    specialization varchar(255),
    biography text,
    email varchar(255) not null,
    primary key(employee_VAT),
    foreign key(employee_VAT) references employee(employee_VAT),
    unique(email)
);

我编写这个触发器的想法是，如果一个人被插入到医生表中，并且与接待员或护士表中的某个人具有相同的employee_VAT，那么它会显示一条错误消息。为此，我编写了以下代码:

create trigger distinct_profession on doctor
after insert
as

if exists(select * from nurse as n, receptionist as r
          join inserted as ins 
          on n.employee_VAT=ins.employee_VAT or r.employee_VAT=ins.employee_VAT)    
begin
    RAISERROR ('Receptionists and nurses cant also be doctors!', 16, 1);
end

但是，这有一个语法错误，并显示错误消息:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on doctor after insert as if exists(select * from nurse as n, rec' at line 1.

您能帮我一下吗？

Answer 1

我认为你的逻辑并不完全正确。

DROP TRIGGER IF EXISTS distinct_profession;
DELIMITER $$
CREATE TRIGGER distinct_profession BEFORE INSERT ON doctor

FOR EACH ROW
BEGIN
  IF NOT EXISTS(SELECT * FROM nurse AS n, receptionist as r
          WHERE
           n.employee_VAT=NEW.employee_VAT or r.employee_VAT=NEW.employee_VAT)    THEN
           SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Receptionists and nurses cant also be doctors!';

  END IF;
END$$
DELIMITER ;

此触发器检查您的选择是否返回任何行，如果没有，它会在插入新行之前引发错误。

这是有道理的如果您需要另一个触发器，则必须更改 BEFORE INSERTT 部分。

您还应该尽可能使用联接，因此也许您有一个连接表