Creating a trigger that records modifications on table A by inserting values in table B

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!

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 ;       

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 ;