Triggers



index
Disabled back button Next Section
printable version

Section 0: Module Objectives or Competencies
Course Objective or Competency Module Objectives or Competency
The student will be able to list and explain the fundamental concepts of stored programs and triggers in SQL, will gain experience developing them, and will learn how to determine when it is appropriate to use them. The student will be able to list and explain the fundamental concepts of triggers in SQL, will gain experience developing them, and will learn how to determine when it is appropriate to use them.
Section 1: Definition

Learning MySQL - Triggers






A trigger is a stored program associated with a table that is automatically activated by the deletion of a record, an insertion of a record, or a modification (update) of a record in a relation.

Types

There are three types of conditions that will initiate a trigger, and thus three main types of triggers you can create: Insert, Update, and Delete. You can have multiples of each of those (or all of them) assigned on a single table.

Insert and update triggers are particularly useful because they can enforce referential integrity constraints and ensure that your data is valid before it enters the table.

All triggers add some overhead, but overhead can accumulate quickly for statements that process large numbers of rows. Avoid placing expensive SQL statements in triggers.

Section 2: Creation

Declaration

Triggers are created using the CREATE TRIGGER syntax:

CREATE TRIGGER trigger_name [BEFORE | AFTER] [INSERT | UPDATE | DELETE] ON table_name
FOR EACH ROW trigger_body

Trigger declarations do not allow a parameter list.

Invocation

The set of statements that make up a trigger are run when an event occurs on a table. They cannot be invoked like procedure or function calls.

Body

Triggers are generally formed of regular SQL statements, although there are some differences.

Variables

Triggers may also declare and use their own set of local variables. DECLARE creates local variables within a trigger.

DECLARE ccResult INTEGER;

NEW and OLD

Don't confuse local variables with user-defined variables. .

  • Local variables are only available within the particular trigger BEGIN ... END block where they are defined.
  • User-defined variables, which begin with an @ symbol and are available anywhere within your connection to the MySQL server, allow you to store a value in one statement and then refer to it later in another statement.
  • Triggers may make use of both local variables and user-defined variables.

Multiple Statements

BEGIN ... END syntax is used for writing compound statements.

Section 3: Restrictions

MySQL does not invoke triggers on foreign key actions (cascading deletes or updates).

Section 4: Limitations

Triggers may invoke stored procedures and stored functions that adhere to the restrictions for triggers.

Section 5: Managing Triggers

To list existing triggers, use SHOW TRIGGERS.

Recall the code of a particular trigger with SHOW CREATE TRIGGER.

To remove triggers from the database, use DROP TRIGGER.

To make changes to a trigger, drop and re-create the routine.

Section 6: Examples

The following examples use the pubs database.

Example: Year-To-Date Sales Calculation

As new sales are recorded and existing sales are modified or deleted in the sales table, these triggers keep the year-to-date sales total current in the titles table.

CREATE TRIGGER YtdSalesInsert BEFORE INSERT ON sales
FOR EACH ROW
UPDATE titles
SET ytd_sales = ytd_sales + NEW.qty
WHERE title_id = NEW.title_id;

CREATE TRIGGER YtdSalesDelete BEFORE DELETE ON sales
FOR EACH ROW
UPDATE titles
SET ytd_sales = ytd_sales - OLD.qty
WHERE title_id = OLD.title_id;

CREATE TRIGGER YtdSalesUpdate BEFORE UPDATE ON sales
FOR EACH ROW
UPDATE titles
SET ytd_sales = ytd_sales - OLD.qty + NEW.qty
WHERE title_id = OLD.title_id;

Notice the UPDATE trigger uses both OLD.qty and NEW.qty to account for the change in quantity.


The following queries affect sales for title_id = 'MC3021', that is, The Gourmet Microwave.

+----------+-----------------------+-----------+
| title_id | title | ytd_sales |
+----------+-----------------------+-----------+
| MC3021| The Gourmet Microwave |22246 |
+----------+-----------------------+-----------+
1 row in set (0.00 sec)

+----------+-----------------------+-----------+
| title_id | title | ytd_sales |
+----------+-----------------------+-----------+
| MC3021| The Gourmet Microwave |23178 |
+----------+-----------------------+-----------+
1 row in set (0.00 sec)

+----------+-----------------------+-----------+
| title_id | title | ytd_sales |
+----------+-----------------------+-----------+
| MC3021| The Gourmet Microwave |22378 |
+----------+-----------------------+-----------+
1 row in set (0.00 sec)

Note the 800 unit decrement in ytd_sales.



Example: Denormalized Derived Attributes

Denormalization is the process of attempting to optimize the read performance of a database by adding redundant data or by grouping data.

In this example, suppose we want a table that contains the total sales for all orders from each customer (customer_sales_totals).

delimiter //
CREATE TRIGGER SalesInsert BEFORE INSERT ON sales
FOR EACH ROW BEGIN
DECLARE row_count INTEGER;
SELECT COUNT(*) INTO row_count FROM customer_sales_totals WHERE customer_id = NEW.customer_id;
IF row_count > 0 THEN
UPDATE customer_sales_totals SET sale_value = sale_value + NEW.sale_value WHERE customer_id = NEW.customer_id;
ELSE
INSERT INTO customer_sales_totals (customer_id, sale_value) VALUES (NEW.customer_id, NEW.sale_value);
END IF;
END//

CREATE TRIGGER SalesUpdate BEFORE UPDATE ON sales
FOR EACH ROW UPDATE customer_sales_totals SET sale_value = sale_value + (NEW.sale_value - OLD.sale_value) WHERE customer_id = NEW.customer_id//

CREATE TRIGGER SalesDelete BEFORE DELETE ON sales
FOR EACH ROW UPDATE customer_sales_totals SET sale_value = sale_value - OLD.sale_value WHERE customer_id = OLD.customer_id//
delimiter ;

Defining stored routines from the MySQL client (command line interface) requires the use of the delimiter command.

  • Here, the statement delimiter is temporarily changed to // so the ; used in the procedure body is passed through to the server rather than being interpreted by the MySQL client.


Example: Data Validation

These triggers validate the identification numbers of authors (Social Security Security numbers) using the Luhn algorithm.

delimiter //
CREATE TRIGGER AuthorIDInsert BEFORE INSERT ON authors
FOR EACH ROW IF LuhnValidate(NEW.au_id) = 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid author identification number'; END IF;//

CREATE TRIGGER AuthorIDUpdate BEFORE UPDATE ON authors
FOR EACH ROW IF LuhnValidate(NEW.au_id) = 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid author identification number'; END IF;//
delimiter ;

Test the trigger by attempting to insert a new author with an invalid Social Security number.


Try again using the following query, and verify that the new author is added when a valid identifier is provided.

INSERT INTO authors (au_id, au_lname, au_fname, contract) VALUES('493-80-0551', 'Waters', 'Clarence', 0);

Section 7: Additional Resources