MySQL: How do I use delimiters in triggers?

by

Part 1

The delimiters are used for source objects like stored procedure/function, trigger or event. All these objects may have a body – code within BEGIN…END clause.

All statement in MySQL scripts should be ended with delimiter, the default is ‘;’. But what to do if source object has body with some statements, e,g:

INSERT INTO table1 VALUES(1);

CREATE PROCEDURE procedure1()
BEGIN
  SET @s="SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse";
  PREPARE stmt2 FROM @s;
  SET @a = 6;
  SET @b = 8;
  EXECUTE stmt2 USING @a, @b;
END;

INSERT INTO table1 VALUES(2);

How many statemants? 3 or 8? The answer is three, because script has two INSERTs and one CREATE PROCEDURE statements. As you see, CREATE PROCEDURE has some internal statements too; we should say to MySQL client that all these statement (inside BEGIN…END) – are part of ONE statement; we can do it with a help of delimiters:

INSERT INTO table1 VALUES(1);

DELIMITER $$

CREATE PROCEDURE procedure1()
BEGIN
  SET @s="SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse";
  PREPARE stmt2 FROM @s;
  SET @a = 6;
  SET @b = 8;
  EXECUTE stmt2 USING @a, @b;
END$$

DELIMITER ;

INSERT INTO table1 VALUES(2);

Note, when your trigger has no BEGIN…END clause, delimiters may be omitted.

Part 2

Without delimiters the statement will be parsed as –

CREATE PROCEDURE procedure1()
BEGIN
  SET @s="SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse";

instead of –

CREATE PROCEDURE procedure1()
BEGIN
  SET @s="SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse";
  PREPARE stmt2 FROM @s;
  SET @a = 6;
  SET @b = 8;
  EXECUTE stmt2 USING @a, @b;
END

More Related Contents:

  1. mysql after insert trigger which updates another table’s column
  2. MySQL – UPDATE query based on SELECT Query
  3. MySQL, update multiple tables with one query
  4. Throw an error preventing a table update in a MySQL trigger
  5. How to abort INSERT operation in MySql trigger?
  6. mysql update column with value from another table
  7. MySQL Trigger after update only if row has changed
  8. MySQL error code: 1175 during UPDATE in MySQL Workbench
  9. update multiple rows using limit in mysql?
  10. How to prepend a string to a column value in MySQL?
  11. MySQL Fire Trigger for both Insert and Update
  12. MySQL trigger On Insert/Update events
  13. Can a MySQL trigger simulate a CHECK constraint? [duplicate]
  14. MySQL error 1436: Thread stack overrun, with simple query
  15. MySQL trigger to update a field to the value of id
  16. How can I set a maximum number of rows in MySQL table?
  17. My MySQL trigger doesn’t work, simple syntax, not complicated
  18. MySql update two tables at once
  19. Advanced MySql Query: Update table with info from another table
  20. What are differences between INSERT and UPDATE in MySQL?
  21. MySQL Trigger: Delete From Table AFTER DELETE
  22. Table is ‘read only’
  23. Does MySQL overwrite a column of same value on update?
  24. Is it Possible to Enforce Data Checking in MySQL using Regular expression
  25. How to create and insert a JSON object using MySQL queries?
  26. The best way to remove value from SET field?
  27. MySQL listen notify equivalent
  28. Update multiple rows with multiple ‘where’ clauses for each individual row
  29. Calling an url from a trigger in mysql
  30. MySQL update statement to store ranking positions

Leave a Comment