Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted September 27, 2012

The Wonderful (and not so Wonderful) Things about MySQL Triggers

By Rob Gravelle

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



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date