Introduction
It is sometimes necessary to create a MySQL trigger, procedure or function with N0C by connecting in SSH.
Note: Do not use phpMyAdmin. Triggers and functions are always user-related. In phpMyAdmin, the user is temporary: therefore, as soon as it is automatically deleted, the triggers and functions are also deleted.
Note :The PlanetHoster Glossary contains explanations on multiple topics and can be consulted to clarify certain terms.
References
This article was written based on MySQL Reference Manual.
Prerequisite
Access to https://mg.n0c.com/en/.
Definitions
A trigger is a mechanism that causes the automatic execution of 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.
A procedure is used to execute a set of SQL statements.
A function is a sub-program consisting of a set of instructions identified by a name, whose execution produces a value that returns to the place where the sub-program was called.
How to Connect to SSH and MySQL
- Connect to your account with an SSH key (if needed, refer to the How to Create an SSH Key and Connect to an Account article).
- Make sure you have a MySQL user by defining them as the main user or creating a user who has privileges on the database (if needed, refer to the Databases article).
- Log in to MySQL with the command below:
mysql -u user bdname -p
Where :
- user must be replaced with your primary username or a MySQL user; and
- bdname must be replaced by the name of the database.
- Enter your password.
- Confirm the connection.
How to Create a Trigger
We will explain how to create a trigger with the MySQL CREATE TRIGGER instruction.
- Connect to SSH and MySQL as explained above (see How to connect to SSH and MySQL).
- 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 trigger name you want to create (trigger_name in our example);
- After BEFORE or AFTER, specify the trigger time (trigger_time), which indicates whether it is executed before or after the row is modified;
- Specify the type of operation that activates the trigger (trigger_event). Allowed 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 is modified in the table;
- DELETE : the trigger is activated as soon as a row is deleted from the table.
- After ON, specify the name of the table (tbl_name in our example) to which the trigger belongs;
- trigger_body is the instruction to be executed as the trigger is activated.
- If you see the message « Query OK, 0 rows affected (0.xyz sec) », the operation is successful (0.xyz are the seconds required to execute the trigger).
Note: the DEFINER command does not appear in the code because, in the case we are dealing with here, it is the user who, by default, executes the CREATE TRIGGER declaration.
OLD and NEW Keywords
It is possible to use the keywords OLD and NEW, which refer to the value before the trigger and the new value respectively.
The trigger can access the values of the column to which the declaration applies. The modifiers OLD and NEW are used to distinguish the value of the columns BEFORE and AFTER the trigger.
For example, if you update the description of the column in the trigger_body, you can access the value of the description before the update (OLD.description) and the new value (NEW.description).
The following table illustrates the availability of these keywords:
| Type of operation (trigger_event) | OLD | NEW |
| INSERT | No | Yes |
| UPDATE | Yes | Yes |
| DELETE | Yes | No |
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 trigger_name AFTER UPDATE ON table_name FOR EACH ROW sql_request_to_run;
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
We will explain how you can create a procedure with the MySQL CREATE PROCEDURE instruction.
- Connect to SSH and MySQL as explained previously (see How to connect to SSH and MySQL).
- In the command line interface, write your code using this syntax:
DELIMITER //
CREATE PROCEDURE ProcedureName(
IN varName VARCHAR(255)
)
BEGIN
SELECT *
FROM table1
WHERE field1 = varName;
END //
DELIMITER ; - In this synthax :
- Temporary redefine the delimitation by typing DELIMITER //1;
- After CREATE PROCEDURE, provide the name of the procedure that you wish to create (ProcedureName);
- State before VARCHAR the strings of the procedure and specify in brackets the number of bytes (in our example, varName is the string and 255 is the number of bytes);
- Write BEGIN to mark the beginning of the procedure;
- In our example, the procedure consists in retrieving selected rows from a table (named here table1) by means of the command SELECT * and in which the field field1 is equal to the string varName;
- Write END // to mark the end of execution of the procedure;
- Return to the default delimitation (;) by typing DELIMITER ;2.
1 A stored procedure consists of several statements 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 whole stored procedure as a single declaration, but several declarations.
2 This makes it possible to separate the declarations and to execute each declaration separately.
How to Create a Function
We will explain how you can create a function in MySQL by coding it with the CREATE FUNCTION statement.
- Connect to SSH and MySQL as explained above (see How to connect to SSH and MySQL).
- 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 ;
- In this syntax:
- Temporary redefine the delimitation by typing DELIMETER $$;
- Provide the name of the function that you want to create (FunctionName) after CREATE FUNCTION;
- Provide the integers before INT (in our example, param1 is an integer);
- Provide the strings before VARCHAR and specify in brackets the number of bytes (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 beginning of the function;
- Write your function statements (– – statements in our example);
- Write return to complete the execution of the function and return a value (the value is “true” in the example);
- Write END $$ to mark the end of execution of the procedure;
- Return to the default delimitation (;) by typing DELIMITER ;.