MariaDB Crash Course - Ben Forta [77]
order_num int NOT NULL ,
PRIMARY KEY (change_id)
) ENGINE=Maria;
Analysis
This table has columns to store the change date and time, the type of change (A for added, U for updated, D for deleted), and the order_num of the order changed.
Now that you have a table to store the change log, you need to create the trigger that updates this new table. Here is the code:
Input
CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW
BEGIN
INSERT INTO orders_log(changed_on, change_type, order_num)
VALUES(Now(),'A', NEW.order_num);
END;
Analysis
CREATE TRIGGER is used to create the new trigger named neworder. 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. When a new order is saved in orders, MariaDB generates a new order number and saves it in order_num. The trigger code obtains this value from NEW.order_num. This is why this trigger must be executed AFTER INSERT, because before the BEFORE INSERT statement is executed the new order_num has not been generated yet. In this example, an INSERT statement is used to add a record of every inserted order into orders_log.
To test this trigger, try inserting a new order, like this:
Input
INSERT INTO orders(order_date, cust_id)
VALUES(Now(), 10001);
The INSERT statement itself does not return anything useful, but it does cause our trigger to be executed. To verify this, let’s see what is in the orders_log table:
Input
SELECT * FROM orders_log;
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;
Analysis
This trigger is similar to the neworder trigger; it logs order deletions. This trigger is executed BEFORE DELETE (or you’d not have access to the order_num).
If you were to delete the order you just inserted, you’d see a second row in the orders_log table reflecting the deletion.
* * *
Note: Multistatement Triggers
Notice that the triggers shown here all use BEGIN and END statements to mark the trigger body. This is actually not necessary in the examples used thus far, although it does no harm. The advantage of using a BEGIN END block is that the trigger would then be able to accommodate multiple SQL statements (one after the other within the BEGIN END block) as you see in the next example.
* * *
Another good use for DELETE triggers is to archive deletions (rows deleted from