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; $$
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.