1. Home
  2. Databases
  3. How to Create a MySQL Trigger and Function by Connecting to an Account with an SSH Key
Searching...

How to Create a MySQL Trigger and Function by Connecting to an Account with an SSH Key

Introduction

It is sometimes necessary to create a trigger, procedure and function on N0C by connecting via SSH and coding them in MySQL.

Note : do not use phpMyAdmin. Triggers and functions are always linked to the user. In phpMyAdmin, the user is temporary: therefore, as soon as the user is automatically deleted, the triggers and functions are automatically deleted too.

Note : The Glossary contains explanations on multiple topics and can be consulted to clarify certain terms.

References

This article is based on MySQL Reference Manual.

Prerequisites

Go to https://mg.n0c.com/en/.

Definitions

A trigger is a mechanism that automatically executes a procedure in a table when a particular event occurs for that table, for example when a user is about to modify the contents of a database. In this article, we’ll use the term trigger, as the term déclencheur is not widely used in IT jargon.

A procedure executes a set of SQL statements.

A function is a sub-program consisting of a set of instructions identified by a name, the execution of which produces a value that refers to the point at which the sub-program was called.

How to Connect Using SSH and MySQL

  1. Log in to your account with an SSH key (how).
  2. Make sure you have a MySQL user by retrieving it as the main user or by creating a user with database privileges (how).
  3. Connect to MySQL with the command below:
mysql -u user dbname -p

Where :

  • user must be replaced by your main username or a MySQL user; and
  • dbname must be replace by the name of the database.
  1. Enter your password.
  2. Confirm the connection.

How to create a trigger

Here we explain how to create a trigger in MySQL, using the CREATE TRIGGER statement.

  1. Connect via SSH and MySQL as explained above (see How to Connect Using SSH and MySQL).
  2. In the command line interface, write your code using this syntax:
CREATE TRIGGER trigger_name

{ BEFORE | AFTER } { INSERT | UPDATE | DELETE }

ON tbl_name FOR EACH ROW

trigger_body;

In this syntax:

  • After CREATE TRIGGER, provide the name of the trigger (trigger_name in the example) that you want to create;
  • After BEFORE or AFTER, specify the trigger action time (trigger_time), which indicates whether the trigger is executed before or after the row is modified;
  • Specify the type of operation that activates the trigger (trigger_event). Permitted values are:
    • INSERT : the trigger is activated as soon as a new row is added to the table;
    • UPDATE : the trigger is activated as soon as a row in the table is modified;
    • DELETE : the trigger is activated as soon as a row is deleted from the table.
  • After ON, specify the name of the table to which the trigger belongs (tbl_name);
  • trigger_body is the instruction to be performed when the trigger is activated.
  1. The operation has worked if you see the message “Query OK, 0 rows affected (0.xyz sec)” (0.xyz being the number of seconds required to execute the trigger).

Note : The DEFINER command does not appear in the code because, in this case, it is the user who, by default, executes the CREATE TRIGGER declaration.

OLD and NEW Keywords

You can use the keywords OLD and NEW, which refer to the pre-trigger value and the new value respectively.

The trigger can access the values of the column concerned by the declaration. The modifiers OLD and NEW are used to distinguish between the value of columns BEFORE and AFTER the operation is triggered.

For example, if you update the column description in trigger_body, you can access the description value before the update (OLD.description) and the new value (NEW.description).

The following table illustrates the availability of these keywords:

Operation type (trigger_event)OLDNEW
INSERTNoYes
UPDATEYesYes
DELETEYesNo

Example

Input :

CREATE TRIGGER log_users AFTER UPDATE ON users FOR EACH ROW INSERT INTO logs(user_id, action) VALUES(NEW.id, 'action name’); 
CREATE TRIGGER nom_du_trigger

   AFTER UPDATE ON nom_de_la_table FOR EACH ROW

   requete_sql_a_lancer;

Output : 

> CREATE TRIGGER log_users AFTER UPDATE ON users FOR EACH ROW INSERT INTO logs(user_id, action) VALUES(NEW.id, 'action name');

Query OK, 0 rows affected (0.020 sec)

How to Create a Procedure

Here we explain how to create a procedure in MySQL, using the CREATE PROCEDURE statement.

  1. Connect via SSH and MySQL as explained above (see How to Connect Using SSH and MySQL).
  2. In the command line interface, write your code using this syntax:
DELIMITER //


CREATE PROCEDURE ProcedureName(
     IN varName VARCHAR(255)
)


BEGIN

     SELECT *
     FROM table1
     WHERE champ1 = varName;

END //


DELIMITER ;
  1. In this syntax:
  • Temporarily redefine the delimiter by typing DELIMETER //1;
  • After CREATE PROCEDURE, enter the name of the procedure (ProcedureName) you wish to create;
  • Before VARCHAR, state the procedure’s character strings and specify the number of bytes in brackets (in our example, varName is the character string and 255 is the number of bytes);
  • Write BEGIN to mark the start of the procedure program;
  • In our example, the procedure consists of retrieving selected rows from a table (here named table1) using the SELECT * command, where field field 1 is equal to the string varName;
  • Write END // to mark the end of procedure execution;
  • Return to the default delimiter (;) by entering DELIMITER ;2.

1 A stored procedure consists of several declarations separated by a semicolon (;). If you use a MySQL client program to define a stored procedure that contains semicolons, the MySQL program will not treat the entire stored procedure as a single statement, but as several statements.

2 This makes it possible to separate declarations and run each one separately.

How to Create a Function

Here we explain how to create a function in MySQL, using the CREATE FUNCTION statement.

  1. Connect via SSH and MySQL as explained above (see How to Connect Using SSH and MySQL).
  2. In the command line interface, write your code using this syntax:
DELIMITER $$
 

 CREATE FUNCTION FunctionName(
     param1 INT,
     param2 VARCHAR(255))
 RETURNS BOOLEAN
 

 BEGIN
  -- statements
  return true;
 END $$
 

 DELIMITER ;
  1. In this syntax:
  • Temporarily redefine the delimiter by typing DELIMETER $$;
  • After CREATE FUNCTION, enter the name of the function (FunctionName) you wish to create;
  • Prefix INT with integers (in our example, param1 is an integer);
  • List strings before VARCHAR and specify the number of bytes in brackets (in our example, param2 is the string and 255 is the number of bytes);
  • Write RETURNS BOOLEAN so that the function returns a true or false value;
  • Write BEGIN to mark the start of the function program;
  • Write your function’s declarations (– – statements in our example);
  • Write return to end execution of the function and return a value (the value is “true” in the example);
  • Write END $$ to mark the end of function execution;
  • Return to the default delimiter (;) by typing DELIMITER ;.
Updated on February 14, 2024

Related Articles