CODEWITHSUNDEEP
mysqlsql
JoNeedsCHelp
JoNeedsCHelp

Reputation: 1

Trigger to prevent simultaneous types

I want to write a trigger to ensure that someone in my database that is a receptionist or a nurse at the clinic cannot simultaneously be a doctor. Note that all these different occupations are different tables with the same foreign key (they're all employees). The code should make it clearer:

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)
);

My idea to write this trigger is if a person is being inserted into the doctor table has the same employee_VAT of someone in the receptionist or nurse table it presents an error message. To do so I wrote this code:

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

However this has a syntax error with error message:

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.

Would you be so kind as to help me?

Upvotes: 0

Views: 43

Answers (1)

nbk
nbk

Reputation: 49395

I don't think that your logic is completely correct.

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 ;

This trigger check if your select returns any Rows and when not it throws an error before inserting the new row.

This make Sense If you need another trigger you have to change the BEFORE INSERTT part.

You should also use Joins whenever possible, so maybe you have a connecting table

Upvotes: 1

Related Questions