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
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.
- For example, a trigger could be defined to perform validation of values to be inserted or to perform calculations on values involved in an update.
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 triggers are fired whenever an insert operation is performed on a table.
- They are often used to enforce referential integrity in situations where another type of constraint is not advisable, or to populate another table during an insert.
- An update trigger will fire each time any row (or rows!) is updated.
- Update triggers can also be used to check field constraints and relationships.
- Delete triggers are fired whenever a delete operation is performed on a table.
Delete triggers are typically used for two reasons:
- The first reason is to prevent deletion of records that will cause data integrity problems if they indeed are deleted. An example of such records is those used as foreign keys to other tables.
- The second reason for using a Delete trigger is to perform a cascading delete operation that deletes children records of a master record.
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.
- Flow control constructs – specifically,
IF
,CASE
,ITERATE
,LEAVE
,LOOP
,WHILE
, andREPEAT
– may appear within triggers to control the order of statement execution. - There are restrictions on which SQL statements may appear in triggers.
- Triggers may consist of multiple statements by using a
BEGIN ... END
compound statement block.
Variables
Triggers may also declare and use their own set of local variables. DECLARE
creates local variables within a trigger.
DECLARE ccResult INTEGER;
SET
assigns values to variables, andSELECT ... INTO
assigns the results of a query that returns a single row to variables.- All local variables must be declared at the beginning of the trigger.
NEW and OLD
-
Within the body of a trigger, the
NEW
andOLD
keywords refer to the table associated with the trigger:OLD.column_name
refers to a column of an existing row before it is updated or deletedNEW.column_name
refers to a column of a new row before it is inserted or an existing row after it is updated
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.
- A compound statement can contain multiple statements, enclosed by the
BEGIN
andEND
keywords. - Each of the statements in a compound statement is terminated by a semicolon
- See this link for details.
Section 3: Restrictions
- Each trigger is associated with exactly one table
-
Each trigger is associated with exactly one time period (
BEFORE
orAFTER
).- A
BEFORE
trigger is activated for each row in the set of affected rows before the trigger event executes. - An
AFTER
trigger is activated for each row in the set of affected rows or for the statement, depending on the trigger granularity, after the trigger event has been completed.
- A
-
Each trigger is associated with exactly one event (
INSERT
,UPDATE
, orDELETE
).- Insert triggers are fired whenever an insert operation is performed on a table.
- An update trigger will fire each time any row (or rows!) is updated.
- Delete triggers are fired whenever a delete operation is performed on a table.
- The trigger event (
INSERT
,UPDATE
, orDELETE
) represents the given type of table operation, not the literal SQL statement of the same name. -
A single SQL statement may invoke multiple triggers, and triggers may
also invoke other triggers.
For example,
INSERT INTO ... ON DUPLICATE KEY UPDATE ...
will invoke aBEFORE INSERT
trigger for every row, followed by either anAFTER INSERT
trigger or both theBEFORE UPDATE
andAFTER UPDATE
triggers, depending on whether there was a duplicate key for the row. - It is not possible to define multiple triggers for the
same time period and event combination for a given table (e.g., only one
BEFORE INSERT
trigger per table); each table can have, at most, six triggers.
MySQL does not invoke triggers on foreign key actions (cascading deletes or updates).
Section 4: Limitations
- Triggers cannot be created on views or temporary tables.
- Triggers cannot start, commit or rollback transactions.
- Triggers cannot return anything.
- you should not design a trigger that modifies the table it is defined on. This is referred to as a recursive query. Triggers cannot (or should not) modify a table that is being used by the statement that invoked the trigger. In other words, do not, for example, issue an UPDATE query on the same table for which an UPDATE trigger is fired. In fact,
- Triggers cannot use prepared statements.
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 drop a trigger if it exists (before creating a new version) use the
IF EXISTS
clause, as inDROP TRIGGER IF EXISTS <trigger_name>;
To make changes to a trigger, drop and re-create the routine.
Section 6: Examples
The following examples use the pubs database.
- We discussed how to create the pubs database in Sample Databases notes.
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.
- Note again, that as changes are made in one table, the triggers make updates to another 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.
- Review the current year-to-date sales for this title with
SELECT title_id, title, ytd_sales FROM titles WHERE title_id = 'MC3021';
+----------+-----------------------+-----------+
| title_id | title | ytd_sales |
+----------+-----------------------+-----------+
| MC3021| The Gourmet Microwave |22246 |
+----------+-----------------------+-----------+
1 row in set (0.00 sec)
- Next, record a new sale with
INSERT INTO sales VALUES ('8042', 'AZ432', '19940913', 932, 'Net 30', 'MC3021');
- Again, review the current year-to-date sales for this title with
SELECT title_id, title, ytd_sales FROM titles WHERE title_id = 'MC3021';
+----------+-----------------------+-----------+
| title_id | title | ytd_sales |
+----------+-----------------------+-----------+
| MC3021| The Gourmet Microwave |23178 |
+----------+-----------------------+-----------+
1 row in set (0.00 sec)
- Since the correct quantity for the new sale should have
been 132 (or 800 units less), we correct the sale with the following query
UPDATE sales SET qty = 132 WHERE ord_num = 'AZ432';
- To check the results, you can again issue the follwoing query:
SELECT title_id, title, ytd_sales FROM titles WHERE title_id = 'MC3021';
+----------+-----------------------+-----------+
| 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.
- Denormalized data is often maintained to improve the performance of critical SQL queries.
- While the code to perform denormalization could be placed within the applications that access the database, every part of every application that modifies the table would need to perform the denormalization.
- Ensuring the denormalized data is kept up-to-date is a nontrivial problem in complex systems.
- The use of triggers, however, can guarantee that the denormalized data will be updated whenever a change is made to the table by any application.
In this example, suppose we want a table that contains the total sales
for all orders from each customer (customer_sales_totals
).
- This allows the most significant customers to be
identified quickly without requiring a costly query on the
sales
table. - A trigger is an ideal way of maintaining the values in this denormalized summary table.
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.
- The validation is performed by the stored function
(
LuhnValidate
) that returns a boolean value. - Implementing the validation algorithm as a stored function is an exercise left to the reader.
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.
- The following query should fail, resulting in
ERROR 1644 (45000): Invalid author identification number
.
INSERT INTO authors (au_id, au_lname, au_fname, contract) VALUES('493-80-0556', 'Waters', 'Clarence', 0);
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);