CREATE DATABASE bug;

use bug;

create table articles(id_ref INT  PRIMARY KEY  NOT NULL AUTO_INCREMENT, price DOUBLE NOT NULL DEFAULT 0);

create table articles2(id_ref INT  PRIMARY KEY  NOT NULL, price DOUBLE NOT NULL DEFAULT 0);

create table art(id INT  PRIMARY KEY  NOT NULL AUTO_INCREMENT, id_ref INT NOT NULL);

INSERT INTO articles(price) VALUES(2.3);

INSERT INTO articles2(id_ref, price) VALUES(-1, 7.3);

CREATE VIEW view_articles as SELECT id_ref, price from articles UNION select id_ref, price from articles2;

DELIMITER //

 CREATE TRIGGER `trg_bins_art`

 BEFORE INSERT ON `art`

 FOR EACH ROW

 BEGIN  

DECLARE priceart DOUBLE DEFAULT NULL;

DECLARE errormsg VARCHAR(255) DEFAULT '';

select price into priceart  

FROM view_articles

where view_articles.id_ref = NEW.id_ref LIMIT 1;

IF (priceart is null) THEN

  SET errormsg = CONCAT("INSERT ERROR : missing line in view_articles [art ref :  ",  NEW.id_ref ,  "]");

  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = errormsg; 

END IF;

END//