Using Triggers

Triggers are used to perform more database operations when a certain type of database operation is initiated. You can use this property of triggers to perform automatic maintenance on your data or to monitor changes that have been made for audit purposes.

To create a new trigger, use the CREATE TRIGGER statement. You must give the trigger a unique name and then provide the timing, action, and table that will cause the trigger to fire. For example, to create a trigger that will fire every time a row is deleted from the products table, you would construct a trigger as follows:

CREATE TRIGGER trigger_name BEFORE DELETE ON products FOR EACH ROW BEGIN
 ...
END

The timing for a trigger can be BEFORE or AFTER, indicating whether the trigger code should be executed immediately before or immediately after the SQL statement that causes the trigger to fire.

The keywords FOR EACH ROW are part of the CREATE TRIGGER syntax and are required in every trigger.

If the trigger body contains more than one line of code, you must enclose the SQL statements with the BEGIN and END keywords. As with stored routines, you also need to redefine the delimiter character if the trigger body contains multiple statementsrefer to Lesson 20, "Stored Routines," for more information.

When a trigger is activated, the stored code can access the data record that caused the trigger event to occur. You can reference two pseudotables in the trigger body. Use OLD to reference deleted values and the previous values from an updated row. Use NEW to reference the new values in the database when using UPDATE or INSERT.

Creating an Audit Trail

A common use for triggers is to create a full audit trail on critical data tables. MariaDB can do much of the hard work for you so you don't have to build this feature into your application.

This example creates an audit trail on the products table. First, however, a new audit table must be created to record the changes as they are made. The table should be created using the following statement:

CREATE TABLE products_audit (
 audit_date TIMESTAMP,
 audit_user VARCHAR(40) NOT NULL,
 audit_action ENUM('update','delete'),
 product_code VARCHAR(10) NOT NULL,
 name VARCHAR(40) NOT NULL, 
 weight DECIMAL(6,2) NOT NULL,
 price DECIMAL(6,2) NOT NULL
);

Two triggers are required to log both DELETE and UPDATE actions on the products table. Each trigger will store a copy of the previous record to the audit table, along with the date and time and the user who issued the SQL command.

The DELETE TRigger is created using the following statement:

MariaDB> CREATE TRIGGER products_delete_trigger
 -> BEFORE DELETE ON products
 -> FOR EACH ROW
 -> INSERT INTO products_audit
 -> (audit_user, audit_action,
 -> product_code, name, weight, price)
 -> VALUES (CURRENT_USER(), 'delete',
 -> OLD.product_code, OLD.name, OLD.weight, OLD.price);
Query OK, 0 rows affected (0.00 sec)

The trigger will fire whenever a row is deleted from products, and it will take place immediately before that row is actually deleted. The OLD pseudotable allows the trigger code to access the old values before they are deleted.

If you delete the MIDI product from the database, a new record will be created in the audit table containing its former values.

MariaDB> DELETE FROM products
 -> WHERE product_code = 'MIDI';
Query OK, 1 row affected (0.00 sec)
MariaDB> SELECT * FROM products_audit \G
*************************** 1. row ***************************
 audit_date: 2006-02-08 22:12:54
 audit_user: chris@localhost audit_action: delete product_code: MIDI
 name: Medium product
 weight: 4.50
 price: 9.99
1 row in set (0.00 sec)

The UPDATE trigger works in a very similar way:

MariaDB> CREATE TRIGGER products_update_trigger
 -> BEFORE UPDATE ON products
 -> FOR EACH ROW
 -> INSERT INTO products_audit
 -> (audit_user, audit_action,
 -> product_code, name, weight, price)
 -> VALUES (CURRENT_USER(), 'update',
 -> OLD.product_code, OLD.name, OLD.weight, OLD.price);
Query OK, 0 rows affected (0.00 sec)

Creating a Data-Maintenance Trigger

Triggers also can maintain values in other areas of your database, based on certain database events. The following example demonstrates this principle.

This trigger implements a dynamic pricing system for the products table, based on supply and demand. The rules for this example are fairly crude: Whenever an order is placed for a product, increase its price by 1% for each unit ordered and decrease the price of the least popular product by the same amount. Of course, you will need to make sure that this rule will not cause a product's price to drop to 0 or become negative.

The steps to implementing this trigger are as follows:

The CREATE TRIGGER statement is shown here in full:

MariaDB> delimiter //
mysql>CREATE TRIGGER supply_and_demand
 -> AFTER INSERT ON order_lines
 -> FOR EACH ROW
 -> BEGIN
 -> DECLARE price_increase DECIMAL(7,2);
 -> SELECT ROUND(price * 0.01 * NEW.quantity, 2)
 -> INTO price_increase
 -> FROM products
 -> WHERE product_code = NEW.product_code;
 ->
 -> UPDATE products
 -> SET price = price + price_increase
 -> WHERE product_code = NEW.product_code;
 ->
 -> UPDATE products
 -> SET price = GREATEST (1.00, price - price_increase)
 -> WHERE product_code = (
 -> SELECT product_code
 -> FROM order_lines
 -> WHERE product_code(=)NEW.product_code
 -> GROUP BY product_code
 -> ORDER BY SUM (quantity)
 -> LIMIT 1
 -> );
 ->
 -> END
 -> //
Query OK, 0 rows affected (0.00 sec)

Let's take this trigger a step at a time. The trigger is set to fire AFTER INSERT on the order_lines tablethis is the table that contains details on how many of each product form part of an order.

The first line of the trigger code declares a local variable named price_increase that will be used to store the amount by which the product sold will be increased and the least popular product will be decreased.

The next step is done in the following statement:

SELECT ROUND(price * 0.01 * NEW.quantity, 2)
INTO price_increase FROM products WHERE product_code = NEW.product_code;

The value of price_increase is calculated from the price of the product that has been sold and the quantity in this order. Note that the NEW pseudotable contains only the columns from order_linesthe table that causes the trigger to fire when an INSERT is performedso you have to query the products table using NEW.product_code to find that product's price.

The next step performs a straightforward update on the products table, increasing the price for NEW.product by price_increase.

Finally, the price of the least popular product is decreased using the following statement:

UPDATE products SET price = GREATEST(1.00, price - price_increase)
WHERE product_code = (
 SELECT product_code
 FROM order_lines
 WHERE product_code(=)NEW.product_code
 GROUP BY product_code
 ORDER BY SUM(quantity)
 LIMIT 1
);

The GREATEST function ensures that the lowest a price can go when decreased is $1. The price value is set for the product_code returned by the subquery. Grouping by product_code and ordering by SUM(quantity)the total number of units ordered for each productthe subquery will return products with those that have the fewest number of orders first.

The output from the following query shows the popularity of the products based on the initial values in order_lines from the sample database:

MariaDB> SELECT product_code, SUM(quantity)
 -> FROM order_lines
 -> GROUP BY product_code
 -> ORDER BY SUM(quantity)
 -> ;
+--------------+---------------+
| product_code | SUM(quantity) |
+--------------+---------------+
| MIDI | 10 |
| MINI | 36 |
| MAXI | 48 |
+--------------+---------------+
3 rows in set (0.00 sec)

The following query shows the initial products and their prices from the sample database:

MariaDB> SELECT * FROM products;
+--------------+----------------+--------+-------+
| product_code | name | weight | price |
+--------------+----------------+--------+-------+
| MINI | Small product | 1.50 | 5.99 |
| MIDI | Medium product | 4.50 | 9.99 |
| MAXI | Large product | 8.00 | 15.99 |
+--------------+----------------+--------+-------+
3 rows in set (0.00 sec)

To test this trigger, let's create a new order line for an existing order:

MariaDB> INSERT INTO order_lines
 -> (order_id, product_code, quantity)
 -> VALUES (1, 'MIDI', 10);
Query OK, 1 row affected (0.00 sec)

When the products table is queried, you can see that the prices have been adjusted accordingly:

+--------------+----------------+--------+-------+
| product_code | name | weight | price |
+--------------+----------------+--------+-------+
| MINI | Small product | 1.50 | 4.99 |
| MIDI | Medium product | 4.50 | 10.99 |
| MAXI | Large product | 8.00 | 15.99 |
+--------------+----------------+--------+-------+
3 rows in set (0.00 sec)

The price of the product ordered (MIDI) has gone up by $1. One percent of its original value is $0.10, and the quantity ordered is 10. The MINI product has been decreased in price by the same $1.

Getting Information About Triggers

To see the triggers on a database, use the SHOW TRIGGERS command. The output contains a list of all the triggers that have been created. The Timing and Event columns show when the trigger will fire, and the Table column contains the associated table name.

A lot of output is produced because the output also contains the entire trigger body code. To view only the triggers for a particular table, use SHOW TRIGGERS LIKE 'table_name'.

Dropping Triggers

A trigger remains always active while it exists in the database. To remove a trigger, you must use the DROP TRIGGER command along with the name you gave to the trigger when you created it.

Disabling Triggers If you want to disable a trigger but think you might need to reactivate it later, you still have to drop the trigger from the database. Be sure to take a copy of the CREATE TRIGGER statement from the output of SHOW TRIGGERS first.

The following example drops the supply_and_demand trigger:

MariaDB> DROP TRIGGER supply_and_demand;
Query OK, 0 rows affected (0.00 sec)

Privileges and Triggers

A user must have the SUPER privilege to use the CREATE TRIGGER command; no separate privilege exists to allow users to create triggers.

Although this means that all triggers are created and owned by a MariaDB superuser, a user who issues a SQL statement that causes a trigger to fire still requires sufficient privileges to execute the trigger code.