Using Triggers - MariaDB Crash Course - page 215

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 exe-

cuted. 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 ,
order_num int NOT NULL ,
PRIMARY KEY (change_id)
) ENGINE=Maria;