driftdrift driftdrift - 3 months ago 9
MySQL Question

What is wrong with this SQL trigger signal?

I'm trying to say the in a given row, Submitter_Employee_ID cant equal Recipient_Id

create trigger employee_no_rate_self before insert on Feedback
for each row
begin
if NEW.Submitter_Employee_id = NEW.Recipient_Employee_id THEN
signal sqlstate '45000'
set message_text = 'Can\'t rate oneself';
end if;
end;

Answer
create table Feedback
(   id int auto_increment primary key,
    message varchar(20) not null, -- brevity is King
    Submitter_Employee_id int not null,
    Recipient_Employee_id int not null
);


drop trigger if exists employee_no_rate_self;
delimiter $$
create trigger employee_no_rate_self before insert on Feedback
 for each row
  begin
  if NEW.Submitter_Employee_id = NEW.Recipient_Employee_id THEN
   signal sqlstate '45000'
   set message_text = 'Can\'t rate oneself';
  end if;
end; $$
delimiter ;

insert Feedback (message,Submitter_Employee_id,Recipient_Employee_id) values
('hello',1,2);
-- success

insert Feedback (message,Submitter_Employee_id,Recipient_Employee_id) values
('bye',2,2);
-- error: Can't rate oneself