Using Triggers - MariaDB Crash Course - page 216

▼ 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 trig-
ger 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;