mysql – Is it necessary to write DELIMITER to create a trigger or to create a stored procedure?

Question:

I ask this question because I ran a trigger without the DELIMITER and it worked (in MySQL)

Answer:

The purpose of the DELIMITER command is to keep open the creation of the sentences that are to compose the procedures to avoid terminating them prematurely, but why?

STORED PROCEDURES

When we write a regular statement in MySQL, we have something like this:

SELECT * FROM tabla;

Where we clearly notice that the symbol that indicates its term is the ;

Now in the case of creating a PA , we have this:

DELIMITER //
CREATE PROCEDURE extraeUsuario(IN idUser INT)
    BEGIN
        SELECT * FROM users WHERE id = idUser;
    END;
//

In the above syntax, we have to use the ; as the end indicator of a sentence, it is replaced by // so that when we are writing this from the console, when ENTER is given, the creation of the same in the SELECT line is not finished.

Once this is done, we can indicate that the entire PA sentence begins where it first appears // and ends until this same symbol re-exists.

TRIGGERS

Case one

When we write a Trigger, we have a statement similar to this:

DELIMITER //
CREATE TRIGGER multiplicaId BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET New.id = (SELECT MAX(id) * 10 FROM users);
END;
//

Where in the same way we occupy the DELIMITER to indicate the beginning and end of a block of code, again to avoid that the execution of this TRIGGER is carried out as soon as it reads the ; from the SET line.

Mainly at the level of the database manager console to omit the use of this operator; can trigger on such an error:

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 4

Although you could create them without the help of this operator, you should take it into account for its implementation when you execute the creation of these structures and have the use of BEGIN and END .

Case 2

A TRIGGER can be explicitly created without the need for the start and end DELIMITER when it lacks the following instructions: BEGIN and END , which indicates that the statement has a simple structure.

The following trigger can be created without the use of the delimiter

CREATE TRIGGER multiplicaIds BEFORE INSERT ON users
    FOR EACH ROW 
        SET New.id = (SELECT MAX(id) * 10 FROM users);

USE OF BEGIN and END

One way to identify if we require a complex structure and that therefore it will need BEGIN and END is if, for example, we will have multiple SELECT within the same PA, for example:

DELIMITER //
CREATE PROCEDURE filtraDatos(IN id1 INT, IN id2 INT)
BEGIN
    SELECT * FROM tabla1 WHERE id = id1;
    SELECT * FROM tabla2 WHERE id = id2;
END;
//

Same PA that we invoke in this way:

CALL filtraDatos(1, 2);

References

Scroll to Top