MySQL 过程/触发器/约束

Question

我需要帮助创建一个有限制的程序，当插入工作人员时，经理需要被分配到一个诊所，并且只需要分配到一个诊所，因此当尝试将另一位经理插入同一家诊所时它应该创建一个触发器，即一名经理已被分配到该诊所。这是我插入变量的代码:

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_staff`(in staff_num char(4),in First_name varchar(40),in surname varchar(30),in street varchar(20), in city varchar(20), in state varchar(2),in zipcode varchar(20),in Phone_no varchar(20),in Date_of_birth  date,in gender_ char(1), in ID_no int(13),in Positon varchar(10),in salary_ double,in ClinicNo char(5))
BEGIN
set sql_safe_updates=0;
set foreign_key_checks=0;
insert into staff(staffNo,sFName,sLname,sStreet,sCity,sState,sZipCode,sTelNo,DOB,gender,ID,position,salary,clinicNo)
values(staff_num,First_name,surname,street,city,state,zipcode,Phone_no,Date_of_birth,gender_,ID_no,Positon,salary_,ClinicNo);

END

Answer 1

以下将在通过选择插入之前进行检查。如果不存在，则插入记录。

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_staff`(in staff_num char(4),in First_name varchar(40),in surname varchar(30),in street varchar(20), in city varchar(20), in state varchar(2),in zipcode varchar(20),in Phone_no varchar(20),in Date_of_birth  date,in gender_ char(1), in ID_no int(13),in Positon varchar(10),in salary_ double,in ClinicNo char(5))
BEGIN
set sql_safe_updates=0;
set foreign_key_checks=0;
IF ( Position = 'Manager' AND EXISTS (select * from staff where clinicNo = ClinicNo and Positon='Manager')) THEN
    //Handle your case
ELSE 
    insert into staff(staffNo,sFName,sLname,sStreet,sCity,sState,sZipCode,sTelNo,DOB,gender,ID,position,salary,clinicNo)values(staff_num,First_name,surname,street,city,state,zipcode,Phone_no,Date_of_birth,gender_,ID_no,Positon,salary_,ClinicNo);
    END IF;
END