01 Oct 2008 Create a mysql trigger

This is a short description on how you can create an easy Trigger in mysql. My trigger will insert todays date when I insert a new database record.

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.

First you have to create a table to work with

CREATE TABLE IF NOT EXISTS `Links` (
  `Id` int(11) NOT NULL auto_increment,
  `DateInserted` date NOT NULL,
  PRIMARY KEY  (`Id`)
)

Then we create the trigger that will insert todays date, curdate(), when you insert a new row

CREATE TRIGGER Links_TriggerDate BEFORE INSERT ON Links
FOR EACH
ROW
SET NEW.DateInserted = curdate( ) 

Triggers is a functionality that was introduced in mysql version 5.0.2
To view all your triggers i mysql

mysql> show triggers;
+--------------------+--------+--------+----------------------------------+--------+---------+----------+----------------+
| Trigger            | Event  | Table  | Statement                        | Timing | Created | sql_mode | Definer        |
+--------------------+--------+--------+----------------------------------+--------+---------+----------+----------------+
| Links_TriggerDate | INSERT | Links | SET NEW.DateInserted = curdate( ) | BEFORE | NULL    |          | root@localhost |
+--------------------+--------+--------+----------------------------------+--------+---------+----------+----------------+
1 row in set (0.00 sec)

Tags: Database, MySQL, triggers

Posted by Hans-Henry Jakobsen