MariaDB Crash Course - Ben Forta [76]
• Every time a customer is added to a database table, check that the phone number is formatted correctly and that the state abbreviation is in uppercase.
• Every time a product is ordered, subtract the ordered quantity from the number in stock.
• Whenever a row is deleted, save a copy in an archive table.
All these examples need to be processed automatically whenever a table change occurs. And that is exactly what triggers are. A trigger is a MariaDB statement (or a group of statements enclosed within BEGIN and END statements) that are automatically executed by MariaDB in response to any of these statements:
• DELETE
• INSERT
• UPDATE
No other MariaDB SQL statements support triggers.
* * *
Note: Only Tables
Triggers are supported only on tables, not on views (and not on temporary tables).
* * *
Creating Triggers
When creating a trigger you need to specify four pieces of information:
• The unique trigger name
• The table to which the trigger is to be associated
• The action that the trigger should respond to (DELETE, INSERT, or UPDATE)
• When the trigger should be executed (before or after processing)
Triggers are created using the CREATE TRIGGER statement. Here is a really simple example (which doesn’t actually do anything useful, but helps explain the syntax needed):
Input
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW
BEGIN
END;
Analysis
CREATE TRIGGER is used to create the new trigger named newproduct. Triggers can be executed before or after an operation occurs, and here AFTER INSERT ON is specified so the trigger will execute after a successful INSERT statement has been executed. The trigger then specifies FOR EACH ROW and the code to be executed for each inserted row. So, whenever a product is added to the products table, this trigger will run, and any code between BEGIN and END will be executed. And as there is nothing between BEGIN and END, well, the trigger will run, but it doesn’t actually do anything.
Triggers are defined per time per event per table, and only one trigger per time per event per table is allowed. As such, up to six triggers are supported per table (BEFORE and AFTER each INSERT, UPDATE, and DELETE). A single trigger cannot be associated with multiple events or multiple tables, so if you need a trigger to be executed for both INSERT and UPDATE operations, you need to define two triggers.
* * *
Note: When Triggers Fail
If a BEFORE trigger fails, MariaDB will not perform the requested operation. In addition, if either a BEFORE trigger or the statement itself fail, MariaDB will not exdcute an AFTER trigger (if one exists).
* * *
Dropping Triggers
By now the syntax for dropping a trigger should be self-apparent. To drop a trigger, use the DROP TRIGGER statement, as seen here:
Input
DROP TRIGGER newproduct;
Analysis
Triggers cannot be updated or overwritten. To modify a trigger it must be dropped and recreated.
Using Triggers
With the basics covered, we now look at each of the supported trigger types, and the differences between them.
INSERT Triggers
INSERT triggers are executed BEFORE or AFTER an INSERT statement is executed. Be aware of the following:
• Within INSERT trigger code, you can refer to a virtual table named NEW to access the rows being inserted.
• In a BEFORE INSERT trigger, the values in NEW may also be updated (allowing you to change values about to be inserted).
• For AUTO_INCREMENT columns, NEW contains 0 before and the new automatically generated value after.
A common use for triggers is to track table changes (audit trails or logs). To try an example, you first need a table to store this information. This next MariaDB SQL statement creates a table to store a log of all changes to the orders table:
Input
CREATE TABLE orders_log
(
change_id int NOT NULL AUTO_INCREMENT,
changed_on datetime NOT NULL ,
change_type char(1) NOT NULL