How to set delay for MySQL trigger/procedure execution?

To set delay, you can use SLEEP(). Let us implement SLEEP() in the procedure execution delay.

First, we will create a stored procedure −

mysql> DELIMITER //
mysql> CREATE PROCEDURE delayInMessage()
   -> BEGIN
   -> SELECT SLEEP(20);
   -> SELECT "AFTER SLEEPING 20 SECONDS, BYE!!!";
   -> END
   -> //
Query OK, 0 rows affected (0.30 sec)
mysql> DELIMITER ;

Now you can call the stored procedure with the help of CALL command. Following is the syntax −

CALL yourStoredProcedureName();

Following is the query to call the above-stored procedure and check the execution delay −

mysql> call delayInMessage();

This will produce the following output −

+-----------+
| SLEEP(20) |
+-----------+
| 0         |
+-----------+
1 row in set (20.00 sec)

+------------------------------------+
| AFTER SLEEPING 20 SECONDS, BYE!!!  |
+------------------------------------+
| AFTER SLEEPING 20 SECONDS, BYE!!!  |
+------------------------------------+
1 row in set (20.01 sec)
Query OK, 0 rows affected (20.04 sec)

Look at the above sample output, the execution delay is 20.01 sec.

Nishtha Thakur

Published on 15-Apr-2019 13:28:18

• Related Questions & Answers
• How can MySQL handle the errors during trigger execution?
• How to schedule tasks in Java to run for repeated fixed-delay execution, beginning after the specified delay
• Schedule a task for repeated fixed delay execution in Java
• Difference Between Trigger and Procedure
• Schedule a task for execution in Java after a given delay
• How to trigger headless test execution in Selenium with Python?
• How to set delay in android?
• Set a delay for the CSS transition effect
• Set new delay time in a MySQL column
• How to schedule tasks in Java to run for repeated fixed-delay execution, beginning at the specified time
• How to schedule tasks in Java to run for repeated fixed-rate execution, beginning after the specified delay
• How to use FOR LOOP in MySQL Stored Procedure?
• How can we write MySQL handler, in a stored procedure, that use SQLSTATE for default MySQL error and exit the execution?
• How to set the order of execution for test methods in Cucumber?
• Set conditions in a MySQL stored procedure