Using Triggers - MariaDB Crash Course - page 217

▼ Output
+-----------+---------------------+-------------+-----------+
| change_id | changed_on | change_type | order_num |
+-----------+---------------------+-------------+-----------+
| 1 | 2011-04-12 10:49:59 | A | 20010 |
+-----------+---------------------+-------------+-----------+
▼ Analysis
orders_logs contains four columns. change_id is the auto incremented
table primary key, changed_on contains the date and time that the change
occurred (generated by the Now() function in the trigger), change_type is A
(order added), and order_num contains the new order number (generated by
MariaDB for the orders table).
Tip
BEFORE or AFTER ? This example used AFTER to execute the trigger after the new
order was created. As a rule, use AFTER if you need to access data that won't exist
until a statement has been processed (for example, to obtain a newly generated order
number). Use BEFORE for any data validation and cleanup (for example, if you want to
make sure that the data inserted into the table was exactly as needed).
DELETE Triggers
DELETE triggers are executed before or after a DELETE statement is executed.
Be aware of the following:
■ Within DELETE trigger code, you can refer to a virtual table named
OLD to access the rows being deleted.
■ The values in OLD are all read-only and cannot be updated.
The following example demonstrates the use of OLD to save rows about to be
deleted into the log table:
▼ Input
CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
INSERT INTO orders_log(changed_on, change_type, order_num)
VALUES(Now(),'D', OLD.order_num);
END;