The wonderful thing about Triggers
Are Triggers are wonderful things – Disney song
There are many kinds of triggers. Some triggers set off addictive behavior. Others are components of weapons like guns. But perhaps the most dangerous triggers of all are those that are found within relational databases. While useful in some contexts, triggers are not the best option in every situation. There are times when a stored procedure is the preferred solution. The trick is of course, when to choose one over the other. And that is what today’s article will attempt to shed some light on.
What Triggers Are and What They Do
A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table. Some uses for triggers are to perform checks of values to be inserted into a table or to perform calculations on values involved in an update. Support for triggers in MySQL was first introduced in version 5.0.2.
A trigger can be defined to activate either before or after an INSERT, DELETE, or UPDATE statement executes for the associated table. For example, you can have a trigger activate before each row that is inserted into a table or after each row that is updated.
Information about all database Triggers are stored in the triggers table of the INFORMATION_SCHEMA database. Hence, the statement Select * from INFORMATION_SCHEMA.triggers; will return all available information on existing triggers:
mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS\G
*************************** 1. row ***************************
TRIGGER_CATALOG: NULL
TRIGGER_SCHEMA: test
TRIGGER_NAME: ins_sum
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: NULL
EVENT_OBJECT_SCHEMA: test
EVENT_OBJECT_TABLE: account
ACTION_ORDER: 0
ACTION_CONDITION: NULL
ACTION_STATEMENT: SET @sum = @sum + NEW.amount
ACTION_ORIENTATION: ROW
ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: NULL
SQL_MODE:
DEFINER: rob@localhost
Another way to retrieve schema information is to use the SHOW TRIGGERS command. Just make sure to set the database first as in USE myDB.
Some Good Reasons to Use Triggers
There are several very good reasons to use triggers, including:
- to audit the changes of data in a database table
- to derive additional data that is not available within a table or within the database. For example, when an update occurs to the quantity column of a product table, you can calculate the corresponding value of the total_price column.
- to enforce referential integrity. For example, when you delete a customer you can use a trigger to delete corresponding rows in the orders table.
- to guarantee that when a specific operation is performed, related actions are performed.
- for centralized, global operations that should be fired for the triggering statement, regardless of which user or database application issues the statement.
Some Good Reasons Not to Use Triggers
There are some equally valid reasons to not use triggers, including:
- they may add workload to the database and cause the system to run slower because they are executed for every user every time the event occurs on which the trigger is created.
- SQL Triggers execute invisibly from client-application, which connects to the database server so it is difficult to figure out what happened in the underlying database layer.
- Triggers are activated by SQL statements only. They are not activated by changes in tables made by APIs that do not transmit SQL statements to the MySQL Server.
You should also bear in mind that, if the logic for your trigger requires much more than 60 lines of SQL code, it’s usually better to include most of the code in a stored procedure and call the procedure from the trigger.
A Practicle Example: Auditing Table Changes
Triggers are a natural choice for auditing database activity where the auditing records do not link back to the table(s) in question via foreign key and auditing activities do not have to be transmitted back to any intermediate application or process.
The following code defines a table for tracking project tasks:
CREATE TABLE `project_tasks`
(
`task_id` int PRIMARY KEY
AUTO_INCREMENT,
`task_name` VARCHAR(255),
`projected_budget` INT,
`task_desciption` TEXT,
`updated_date` DATETIME
);
Whenever a record is added to the above table, we want to track the following information about the user, fields, and when the transaction was committed:
CREATE TABLE `audit` (
`AuditId` int(8)
NOT NULL auto_increment,
`UserName` varchar(50) NOT NULL,
`TableName` varchar(50) NOT NULL,
`FieldName` varchar(50) NOT NULL,
`OldValue` longtext,
`NewValue` longtext,
`DTAdded` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`AuditId`)
);
The syntax for our INSERT trigger would look something like the following:
CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name
FOR EACH ROW
trigger_statement
The trigger_time must be either BEFORE or AFTER. The difference between the two is that a BEFORE trigger is activated by the attempt to insert or modify the row, regardless of whether the attempt subsequently succeeds. On the other hand, an AFTER trigger is executed only if any existing BEFORE triggers and the row operation both execute successfully.
Triggers can only be activated via the INSERT, UPDATE, and DELETE operations. Hence, the trigger_event must contain one of those three values.
Here then is our complete trigger CREATE statement. Note that we need to specify a delimiter to tell MySQL about the end of line is not the Enter key. Some IF logic is required for non-mandatory fields in order to avoid auditing empty data:
delimiter $$
CREATE TRIGGER audit AFTER INSERT ON project_tasks
FOR EACH ROW BEGIN
INSERT INTO `audit`
(`UserName`, `TableName`,
`FieldName`, `OldValue`, `NewValue`)
VALUES (USER(),`project_tasks`, `task_name`,
`new record`, NEW.task_name);
IF (NEW.projected_budget <> 0
AND NEW.projected_budget IS NOT NULL) THEN
INSERT INTO `audit`
(`UserName`, `TableName`,
`FieldName`, `OldValue`, `NewValue`)
VALUES (USER(),`project_tasks`, `projected_budget`,
`new record`, NEW.projected_budget);
END IF;
IF (NEW.task_desciption <> '')
AND (NEW.task_desciption IS NOT NULL) THEN
INSERT INTO `audit`
(`UserName`, `TableName`,
`FieldName`, `OldValue`, `NewValue`)
VALUES (USER(),`project_tasks`, `task_desciption`,
`new record`, NEW.task_desciption);
END IF;
END;
$$
Conclusion
Triggers certainly do have a place in relational database design, so long as one doesn’t cultivate an overdependence on them. The example presented here today was only a basic one. In the next article, we’ll see how to combine the power of stored procedures with triggers as well as how to execute the latter on multiple data operations.
See all articles by Rob Gravelle
IE 7
Firefox 2.0
Comment and Contribute
