MySQL - What is wrong with this trigger?

Question

I am trying to create this trigger and am getting an error for the UPDATE line.

DROP TRIGGER IF EXISTS upd_signedup;

CREATE TRIGGER upd_signedup
BEFORE INSERT ON tbl_users
FOR EACH ROW
BEGIN
  UPDATE tbl_user_stats SET signups = signups + 1;
END

EDIT: I have set the delimiter to $$ but can't create this trigger:

 drop trigger if exists upd_signedup$$

 CREATE TRIGGER upd_signedup
 BEFORE INSERT ON tbl_users
 FOR EACH ROW
 BEGIN
  UPDATE tbl_user_stats SET signups = signups + 1$$
 END

What MySQL client are you executing this with? Did you change the statement delimiter as appropriate for your client? — Michael Berkowski, Nov 21 '13 at 14:21
#1064 - 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 5 — user2121620, Nov 21 '13 at 14:25
I believe PHPMyAdmin has a field to set the delimiter. See http://stackoverflow.com/questions/8080681/store-procedures-in-phpmyadmin for an example of what your statements should look like (where `$$` is the chosen delimiter in those examples) — Michael Berkowski, Nov 21 '13 at 14:27
Ok, I set the delimiter to $$ and was able to create the trigger, after I removed the UPDATE statement. I am still receiving an error if I include the update statement. (check the manual ... for the right syntax to use near '' at line 5). — user2121620, Nov 21 '13 at 14:33
The update should end with `;`. Add another `$$` after `END` — mavroprovato, Nov 21 '13 at 14:49

score 2 · Answer 1 · edited May 23 '17 at 11:56

It seems you misunderstood the concept of using DELIMITER while defining triggers.

;(semi colon) is a regular delimiter, an indicator for the end of an executable statement. But when you are defining a trigger or a stored procedure you define body with multiple executable statements like variable declarations and SQL statements.

The use of ; indicates the SQL engine that the statement reached an end and it is time to compile and execute it. But unless the body of trigger or stored procedure ENDs, there is no meaning in executing internal statements of trigger/sp body. To stop processing such statements we use a custom DELIMITER like // or $$ or something that you are comfortable with and frequently do not use in the body part of trigger/sp definition. MySQL then understands that a statement ends only when it finds your custom defined DELIMITER say $$ or //.

An example is shown below:

mysql>
mysql> set @cnt=0;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> -- drop trigger if exists trig_bef_del_on_tbl;
mysql> delimiter //
mysql> create trigger trig_bef_del_on_tbl before delete on tbl
    ->   for each row begin
    ->     set @cnt = if(@cnt is null, 1, (@cnt+1));
    ->
    ->     /* for cross checking save loop count */
    ->     insert into rows_affected values ( @cnt );
    ->   end;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql>
mysql> -- now let us test the delete operation
mysql> delete from tbl where i like '%1%';
Query OK, 3 rows affected (0.02 sec)

Refer to:

My answer to a query on mysql delete triggers
Create Trigger Syntax

score 0 · Answer 2 · answered Nov 21 '13 at 15:27

0

Trigger's body has only one command, so you can use this simple syntax, without BEGIN-END clause and without DELIMITERs:

CREATE TRIGGER upd_signedup
  BEFORE INSERT ON tbl_users
  FOR EACH ROW
  UPDATE tbl_user_stats SET signups = signups + 1;

answered Nov 21 '13 at 15:27

Devart

119,203
23
166
186

If I want to eventually add a SELECT statement before the UPDATE statement would I need the BEGIN-END clause? – user2121620 Nov 21 '13 at 15:29
Yes, you will need to add that clause. Note, it is possible that SELECT and UPDATE statements can be united in one common UPDATE statement. – Devart Nov 21 '13 at 15:34

MySQL - What is wrong with this trigger?

2 Answers2