What Does Delimiter // Do in a Trigger

What does DELIMITER // do in a Trigger?

It changes the statement delimiter from ; to //. This is so you can write ; in your trigger definition without the MySQL client misinterpreting that as meaning you're done with it.

Note that when changing back, it's DELIMITER ;, not DELIMITER; as I've seen people try to do.

MySQL: How do I use delimiters in triggers?

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

Delimiters in MySQL

Delimiters other than the default ; are typically used when defining functions, stored procedures, and triggers wherein you must define multiple statements. You define a different delimiter like $$ which is used to define the end of the entire procedure, but inside it, individual statements are each terminated by ;. That way, when the code is run in the mysql client, the client can tell where the entire procedure ends and execute it as a unit rather than executing the individual statements inside.

Note that the DELIMITER keyword is a function of the command line mysql client (and some other clients) only and not a regular MySQL language feature. It won't work if you tried to pass it through a programming language API to MySQL. Some other clients like PHPMyAdmin have other methods to specify a non-default delimiter.

Example:

DELIMITER $$
/* This is a complete statement, not part of the procedure, so use the custom delimiter $$ */
DROP PROCEDURE my_procedure$$

/* Now start the procedure code */
CREATE PROCEDURE my_procedure ()
BEGIN    
  /* Inside the procedure, individual statements terminate with ; */
  CREATE TABLE tablea (
     col1 INT,
     col2 INT
  );

  INSERT INTO tablea
    SELECT * FROM table1;

  CREATE TABLE tableb (
     col1 INT,
     col2 INT
  );
  INSERT INTO tableb
    SELECT * FROM table2;
  
/* whole procedure ends with the custom delimiter */
END$$

/* Finally, reset the delimiter to the default ; */
DELIMITER ;

Attempting to use DELIMITER with a client that doesn't support it will cause it to be sent to the server, which will report a syntax error. For example, using PHP and MySQLi:

$mysqli = new mysqli('localhost', 'user', 'pass', 'test');
$result = $mysqli->query('DELIMITER $$');
echo $mysqli->error;

Errors with:

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 'DELIMITER $$' at line 1

Delimiter and Trigger Inquiry

DELIMITER creates a demarcation of the whole block. It uses ?? as the string if you will for the whole wrapper at the beginning and end. At the end, ; is set as the DELIMITER that we are all used to typing for end-of-statement. DELIMITERS are not used in SQLFiddle or PHPMyAdmin, but they are on MySQL Workbench and others. So it is a client-side thing. Depends what you are using.

Before an actual UPDATE to table Student_Results it performs an INSERT (using a not-very used style of an insert stmt) to table Result_Changes specifying 5 column names.

The UPDATE has not technically occurred yet in Student_Results. But the trigger has access to the rows about to be updated there and they are referenced with the special "row name" called OLD.

The FOR EACH ROW could loop for 1 to N times.

Mysql create trigger syntax error used DELIMITER $$

You have written a Trigger code in TSQL (Microsoft SQL Server); however you are using MySQL. It is recommended to check the Trigger documentation at: https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html

DELIMITER $$

/* AFTER INSERT comes before the table name on which Trigger is being created */
CREATE TRIGGER key_as_linksp AFTER INSERT ON tbl_baivietsp

FOR EACH ROW BEGIN /* Instead of AS, Trigger block starts with FOR EACH ROW BEGIN */

  IF EXISTS ( select 1 from tbl_baivietsp t 
              inner join INSERTED i 
                on i.LINK_SP LIKE CONCAT('%', t.LINK_SP ,'%')) THEN /* THEN is missing */
      /* Throw Exception */
      SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Duplicate Data';
  END IF; /* we use END IF instead of END to end an IF block */

END $$  /* Trigger block ends with END clause */
DELIMITER ;

In the case of MySQL, we use SIGNAL .. SET MESSAGE_TEXT .. to throw an exception inside the Trigger.

How do I set sql delimiter through R code?

It turns out that in order to execute an sql trigger through R using the DBI package, one does not need to set and unset the delimiter. We can directly execute the trigger command.

This is unlike what needs to be done while setting a triggers through SQL command line where, since the trigger syntax itself includes a semicolon ;, in order to avoid conflict with the default SQL delimiter which is also ; we temporarily set the delimiter to a lesser used special character such as // with a command such as

delimiter //

and then revert back to the default delimiter with

delimiter ;

which need not be done when trigger is executed through DBI package of R.

When are MYSQL delimiters useful?

DELIMITER is not part of the SQL language. It's a command of the official command-line tool:

If you use the mysql client program to define a stored program containing semicolon characters, a problem arises. By default, mysql itself recognizes the semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to the server.

To redefine the mysql delimiter, use the delimiter command.

There're some third-party programs that also implement a delimiter command in order to be able to run complex MySQL scripts (e.g. HeidiSQL).

If you are running your script in a context that doesn't allow multiple statements (e.g. a typical PHP application) the command will neither exist nor be needed at all.

---

Related Topics