mysql CREATE TRIGGER after insert on Update column with count

Question

it gives error. I don't understand why.

CREATE TRIGGER `estatecat_piece` AFTER INSERT ON `estate` 
FOR EACH ROW 
BEGIN 
UPDATE estate_category set piece = (Select count(*) from estate where estate.estatecat_id=new.estatecat_id);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

-EDIT-

agian error...

mysql> DELIMITER ; CREATE TRIGGER `estatecat_piece` AFTER INSERT ON `estate`  FOR EACH ROW  BEGIN  UPDATE estate_category set piece = (Select count(*) from estate where estate.estatecat_id=new.estatecat_id); END; DELIMITER ;
mysql> 
mysql> show triggers;
Empty set (0,00 sec)

_ LAST ATTEMPT'S ERROR _

mysql> DELIMETER $$                                                                 -> CREATE TRIGGER estatecat_piece
    -> AFTER INSERT ON estate FOR EACH ROW 
    -> BEGIN
    -> UPDATE estate_category set piece = (Select count(*) from estate where estate.estatecat_id=new.estatecat_id) where estatecat_id=new.estatecat_id; 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMETER $$
CREATE TRIGGER estatecat_piece
AFTER INSERT ON estate FOR EACH ROW ' at line 1

Answer 1

The immediate issue with your first statement is that it has BEGIN without a corresponding END. In this case, END should be at the very end of the CREATE TRIGGER statement.

Once you add the END keyword, the statement becomes technically correct, but you will have trouble executing it in MySQL. The reason is that semicolons, while being standard statement delimiters in SQL, are also treated by MySQL in a special way. MySQL splits the input text at semicolons and sends each part of the text to the server separately. The server thus receives incomplete, syntactically incorrect pieces and returns an error. See here for more details:

• Defining Stored Programs

So, to prevent MySQL from doing that, you need to instruct it to use a different symbol as a delimiter – that is what the DELIMITER command is for. Consequently, your second attempt should be something like this:

DELIMITER $$

CREATE TRIGGER `estatecat_piece`
AFTER INSERT ON `estate`
FOR EACH ROW
BEGIN
  UPDATE estate_category set piece = (Select count(*) from estate where estate.estatecat_id=new.estatecat_id);
END$$

DELIMITER ;

The first DELIMITER command tells MySQL to parse the input text until $$ is encountered from that point on. Your CREATE TRIGGER should, therefore, end with the $$ symbol so that MySQL can consume it in its entirety and send the whole statement to the server. Finally, the second DELIMITER command restores the standard delimiter for MySQL to resume normal processing of your commands.

There is also a simpler solution, but it works only in cases similar to yours. The trigger body in your case consists of a single statement. That allows you to omit the keywords BEGIN and END from your statement and, as a result, avoid using the DELIMITER command at all:

CREATE TRIGGER `estatecat_piece`
AFTER INSERT ON `estate`
FOR EACH ROW
UPDATE estate_category set piece = (Select count(*) from estate where estate.estatecat_id=new.estatecat_id);

The above trigger will work exactly the same.

On a different note, your UPDATE statement may have a flaw: as currently written, it will update the piece column in every row with the same row count – that of the category matching the inserted row. If the table has multiple rows, each for a different category, you probably need to introduce a filter on new.estatecat_id to your UPDATE statement, similar to the filter in the subquery, in order to update only the corresponding piece value:

UPDATE
  estate_category
SET
  piece = (
    SELECT
      COUNT(*)
    FROM
      estate
    WHERE
      estate.estatecat_id=new.estatecat_id
  )
WHERE
  ... /* specify the necessary column name */ = new.estatecat_id
;