I’m having problems with the following trigger that records when an insertion with attribute ‘numero’= 4 is made on table A by inserting into table B who made the modification and the number of entries with attribute ‘numero’= 4 in table A. The problem seems to be the select * count but I can’t figure it out. Any help would be greatly appreciated!
x
DELIMITER $
CREATE TRIGGER t_BI
AFTER INSERT ON A
FOR EACH ROW
BEGIN
IF NEW.numero=4 THEN
INSERT INTO B(name,nbLoc)
VALUE(CURRENT_USER(),SELECT COUNT(*) FROM A WHERE numero = 4);
END IF;
END
$
DELIMITER ;
Advertisement
Answer
Try to store your count value in a variable first, and use that variable in the INSERT
DELIMITER $
CREATE TRIGGER t_BI
AFTER INSERT ON A
FOR EACH ROW
BEGIN
DECLARE cnt INT;
IF NEW.numero=4 THEN
SET cnt = ( SELECT COUNT(*) FROM A WHERE numero = 4) ;
INSERT INTO B(name,nbLoc)
VALUE(CURRENT_USER(), cnt);
END IF;
END
$
DELIMITER ;