Can't create MySQL Trigger with several tables-CodePudding

I've got database with 3 tables delivery, company, details. Company have column with rating from 1 to 10 and if rating more than 5 we can understand that this company is reliable and in details if price more than 1000 it is expensive detail. Table delivery is connecting table for company and details Now I'm trying create trigger which will block Insert when somebody trying to add in table Delivery expensive detail with unrelaible company, but I can't understand how create Trigger using data from different tables. Help me please!^^

I'm using mySQL

DELIMITER //
CREATE TRIGGER before_insert_1
BEFORE INSERT
ON delivery
FOR EACH ROW
IF company.rating < 5 AND detail.Det_Price > 1000 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Unreliable company';
END IF //
DELIMITER ;

CodePudding user response：

You should review https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html paying particular attention to the discussion of NEW. values.

A simple version of the trigger might be like

DELIMITER //
CREATE TRIGGER before_insert_1
BEFORE INSERT
ON delivery
FOR EACH ROW
begin
declare vrating int;
declare vprice  int;

select company.rating into vrating from company where company.id = new.company_id;
select detail.det_price into vprice from detail where detail.? = new.?;
IF vrating < 5 AND vPrice > 1000 THEN
   SIGNAL SQLSTATE '45000'
   SET MESSAGE_TEXT = 'Unreliable company';
END IF ;
end //
DELIMITER ;

but since you didn't publish table definitions I can't tell you exactly how the selects should be.