Jump to: navigation, search

MySQL Triggers

From w3cyberlearnings

Contents

Create Tables

mysql> CREATE TABLE order_list
    -> ( order_id INT NOT NULL AUTO_INCREMENT,
    ->   customer_name VARCHAR(200) NOT NULL,
    ->   price DOUBLE NOT NULL,
    ->   PRIMARY KEY(order_id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE order_logs
    -> (
    -> log_id INT NOT NULL AUTO_INCREMENT,
    -> order_id INT NOT NULL,
    -> activity VARCHAR(255) NOT NULL,
    -> log_date DATETIME NOT NULL,
    -> PRIMARY KEY(log_id)
    -> );
Query OK, 0 rows affected (0.02 sec)

Insert sample records

mysql> INSERT INTO order_list (order_id,
    -> customer_name, price) VALUES(null,'David',20),
    -> (null,'Jason',30),
    -> (null,'George',29),
    -> (null,'Anna',30),
    -> (null,'Elizabet',21);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM order_list;
+----------+---------------+-------+
| order_id | customer_name | price |
+----------+---------------+-------+
|        1 | David         |    20 | 
|        2 | Jason         |    30 | 
|        3 | George        |    29 | 
|        4 | Anna          |    30 | 
|        5 | Elizabet      |    21 | 
+----------+---------------+-------+
5 rows in set (0.01 sec)

Trigger and usage

     Sytax: NEW.colum_field
     Example:
         NEW.order_id
         New.customer_name
	 New.price

     New is used for the newly insert record,
       

     Sytax: OLD.colum_field
     Example:
         OLD.order_id
         OLD.customer_name
         OLD.price
     
     OLD is used for the old record or the current record in the table. NOT THE NEW RECORD!
     OLD is already existed on the table!!!

List All the triggers

This command uses to show triggers on the current database. 

mysql> SHOW TRIGGERS\G
*************************** 1. row ***************************
  Trigger: order_list_beforeInsert
    Event: INSERT
    Table: order_list
Statement: BEGIN
     IF NEW.price > 40 THEN
        SET NEW.customer_name = CONCAT(New.customer_name,'*');
     ELSE
        SET NEW.customer_name = CONCAT(NEW.customer_name,'(less than 40)');
     END IF;
END
   Timing: BEFORE
  Created: NULL
 sql_mode: 
  Definer: root@localhost
....

List Trigger with LIKE

mysql> SHOW TRIGGERS LIKE '%order%'\G
*************************** 1. row ***************************
  Trigger: order_list_beforeInsert
    Event: INSERT
    Table: order_list
Statement: BEGIN
     IF NEW.price > 40 THEN
        SET NEW.customer_name = CONCAT(New.customer_name,'*');
     ELSE
        SET NEW.customer_name = CONCAT(NEW.customer_name,'(less than 40)');
     END IF;
END
   Timing: BEFORE
  Created: NULL
 sql_mode: 
  Definer: root@localhost
....

Remove Trigger From Database

order_logDelete is the name of the trigger! 

mysql> DROP TRIGGER IF EXISTS order_logDelete;
Query OK, 0 rows affected (0.00 sec)

Trigger After Insert

This trigger will be called to insert to the order_logs every time a new record insert into the order_list table. The NEW.order_id is the new order_id that is just generated while it inserts into the order_list table. 


mysql> DELIMITER $$
mysql> CREATE TRIGGER order_trigger
    -> AFTER INSERT ON order_list
    -> FOR EACH ROW
    -> BEGIN
    ->    INSERT INTO order_logs (log_id,
    ->        activity,log_date,order_id)
    ->        VALUES(null,'insert record',now(),NEW.order_id);
    -> END $$
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

Insert record into the order_list table to test after insert trigger

mysql> INSERT INTO order_list VALUES(null,'Brady',27);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM order_list;
+----------+---------------+-------+
| order_id | customer_name | price |
+----------+---------------+-------+
|        1 | David         |    20 | 
|        2 | Jason         |    30 | 
|        3 | George        |    29 | 
|        4 | Anna          |    30 | 
|        5 | Elizabet      |    21 | 
|        6 | Brady         |    27 | 
+----------+---------------+-------+
6 rows in set (0.00 sec)

mysql> select * from order_logs;
+--------+-------------+---------------+---------------------+
| log_id | order_id    | activity      | log_date            |
+--------+-------------+---------------+---------------------+
|      1 |           6 | insert record | 2012-02-10 20:31:24 | 
+--------+-------------+---------------+---------------------+
1 row in set (0.00 sec)

Trigger After Update

Insert into the order_logs file when the order_list table is updated. 

mysql> DELIMITER $$
mysql> CREATE TRIGGER order_triggerupdate
    ->   AFTER UPDATE ON order_list FOR EACH ROW
    ->   BEGIN
    ->       INSERT INTO order_logs(log_id,
    ->         activity,log_date,order_id)
    ->        VALUES(null,'update record',now(),NEW.order_id);
    ->   END $$
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

Test the After Update Trigger 

mysql> UPDATE order_list SET price=34 WHERE order_id=6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM order_logs;
+--------+-------------+---------------+---------------------+
| log_id | order_id | activity      | log_date            |
+--------+-------------+---------------+---------------------+
|      1 |           6 | insert record | 2012-02-10 20:31:24 | 
|      2 |           6 | update record | 2012-02-10 20:44:26 | 
+--------+-------------+---------------+---------------------+
2 rows in set (0.01 sec)

Trigger After Delete

When delete a record in the order_list, the order_logs table is also inserted the deleted records from the order_list table. 

mysql> CREATE TRIGGER order_logDelete
    -> AFTER DELETE ON order_list FOR EACH ROW
    -> BEGIN
    ->     INSERT INTO order_logs(log_id,
    ->     activity, log_date, order_id)
    ->     VALUES(null,
    ->          CONCAT('Deleted: ', old.price,' customer: ', old.customer_name), 
    ->          NOW(), old.order_id);
    -> END $$
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

Delete a Record on order_list to test the AFTER DELETE trigger

mysql> DELETE FROM order_list WHERE order_id=6;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM order_logs;
+--------+-------------+-----------------------------+---------------------+
| log_id | order_id | activity                    | log_date            |
+--------+-------------+-----------------------------+---------------------+
|      1 |           6 | insert record               | 2012-02-10 20:31:24 | 
|      2 |           6 | update record               | 2012-02-10 20:44:26 | 
|      3 |           6 | Deleted: 34 customer: Brady | 2012-02-10 20:56:06 | 
+--------+-------------+-----------------------------+---------------------+
3 rows in set (0.00 sec)

Delete multiple records

Delete two records and we can see that there are two records insert on the order_logs table. 

mysql> SELECT * FROM order_list;
+----------+---------------+-------+
| order_id | customer_name | price |
+----------+---------------+-------+
|        1 | David         |    20 | 
|        2 | Jason         |    30 | 
|        3 | George        |    29 | 
|        4 | Anna          |    30 | 
|        5 | Elizabet      |    21 | 
+----------+---------------+-------+
5 rows in set (0.00 sec)

mysql> DELETE FROM order_list WHERE price=30;
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM order_logs;
+--------+-------------+-----------------------------+---------------------+
| log_id | order_id    | activity                    | log_date            |
+--------+-------------+-----------------------------+---------------------+
|      1 |           6 | insert record               | 2012-02-10 20:31:24 | 
|      2 |           6 | update record               | 2012-02-10 20:44:26 | 
|      3 |           6 | Deleted: 34 customer: Brady | 2012-02-10 20:56:06 | 
|      4 |           2 | Deleted: 30 customer: Jason | 2012-02-10 20:57:45 | 
|      5 |           4 | Deleted: 30 customer: Anna  | 2012-02-10 20:57:45 | 
+--------+-------------+-----------------------------+---------------------+
5 rows in set (0.00 sec)

Trigger before Insert

Check the newly inserted price of the order, whenever the price is larger than 40, the star is placed at the end of the customer name (i.e customer*, Bob*). When the price is less than 40 than in the customer name contains the word less than 40. 

ysql> DELIMITER $$
mysql> CREATE TRIGGER order_list_beforeInsert
    -> BEFORE INSERT ON order_list
    -> FOR EACH ROW
    ->    BEGIN
    ->      IF NEW.price > 40 THEN
    ->         SET NEW.customer_name = CONCAT(New.customer_name,'*');
    ->      ELSE
    ->         SET NEW.customer_name = CONCAT(NEW.customer_name,'(less than 40)');
    ->      END IF;
    -> END$$
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;

Insert a new record to test the trigger before insert

mysql> INSERT INTO order_list (order_id, customer_name,price)
    -> VALUES(null,'Jocy',41),
    -> (null,'Mimi',29);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM order_list;
+----------+--------------------+-------+
| order_id | customer_name      | price |
+----------+--------------------+-------+
|        1 | David              |    20 | 
|        8 | Mimi(less than 40) |    29 | 
|        3 | George             |    29 | 
|        7 | Jocy*              |    41 | 
|        5 | Elizabet           |    21 | 
+----------+--------------------+-------+
5 rows in set (0.00 sec)

Trigger Before Update

mysql> DELIMITER $$
mysql> CREATE
    -> TRIGGER order_log_before_udpate
    -> BEFORE UPDATE ON order_list
    -> FOR EACH ROW
    -> BEGIN
    ->    INSERT INTO order_logs(log_id, order_id,activity,log_date)
    ->        VALUES(null,
    ->        OLD.order_id,
    ->        CONCAT('Update:',OLD.customer_name,'Old price:',OLD.price,'New price:',New.price),
    ->         now()
    ->     );
    -> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;

Update Record To Test The Trigger Before Update

mysql> UPDATE order_list SET price=44 WHERE order_id=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM order_list;
+----------+--------------------+-------+
| order_id | customer_name      | price |
+----------+--------------------+-------+
|        1 | David              |    20 | 
|        8 | Mimi(less than 40) |    29 | 
|        3 | George             |    29 | 
|        7 | Jocy*              |    41 | 
|        5 | Elizabet           |    44 | 
+----------+--------------------+-------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM order_logs;
+--------+-------------+-----------------------------------------+---------------------+
| log_id | order_id    | activity                                | log_date            |
+--------+-------------+-----------------------------------------+---------------------+
|      1 |           6 | insert record                           | 2012-02-10 20:31:24 | 
|      2 |           6 | update record                           | 2012-02-10 20:44:26 | 
|      3 |           6 | Deleted: 34 customer: Brady             | 2012-02-10 20:56:06 | 
|      4 |           2 | Deleted: 30 customer: Jason             | 2012-02-10 20:57:45 | 
|      5 |           4 | Deleted: 30 customer: Anna              | 2012-02-10 20:57:45 | 
|      6 |           7 | insert record                           | 2012-02-10 22:42:12 | 
|      7 |           8 | insert record                           | 2012-02-10 22:42:12 | 
|      8 |           5 | Update:ElizabetOld price:21New price:44 | 2012-02-10 22:55:35 | 
|      9 |           5 | update record                           | 2012-02-10 22:55:35 | 
+--------+-------------+-----------------------------------------+---------------------+
9 rows in set (0.00 sec)

Create order_address Table

mysql> CREATE TABLE order_address
    -> (
    -> order_address_id INT NOT NULL AUTO_INCREMENT,
    -> order_id INT NOT NULL,
    -> address VARCHAR(255) NOT NULL,
    -> PRIMARY KEY(order_address_id)
    -> );
Current database: book

Query OK, 0 rows affected (0.04 sec)

Insert Sample Record

mysql> INSERT INTO order_address(order_address_id, order_id,address)
    -> VALUES(null,5,'2450 Lake Rd, Apt 148013, Huntsville, Tx 77340'), 
    -> (null,7,'2104 Makam Rd, Apt 134, Houston, Tx 77333'),
    -> (null,3,'1300 Garland Rd, Apt 12, Dallas, Tx 77111');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM order_address;
+------------------+----------+------------------------------------------------+
| order_address_id | order_id | address                                        |
+------------------+----------+------------------------------------------------+
|                1 |        5 | 2450 Lake Rd, Apt 148013, Huntsville, Tx 77340 | 
|                2 |        7 | 2104 Makam Rd, Apt 134, Houston, Tx 77333      | 
|                3 |        3 | 1300 Garland Rd, Apt 12, Dallas, Tx 77111      | 
+------------------+----------+------------------------------------------------+

Trigger Before Delete

mysql> DELIMITER $$
mysql> CREATE TRIGGER order_deleted_address
    -> BEFORE DELETE ON order_list FOR EACH ROW
    -> BEGIN
    ->     DELETE FROM order_address WHERE OLD.order_id = order_id;
    -> END $$
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;

Test the Trigger Before Delete

mysql> DELETE FROM order_list where order_id=7;
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM order_address;
+------------------+----------+------------------------------------------------+
| order_address_id | order_id | address                                        |
+------------------+----------+------------------------------------------------+
|                1 |        5 | 2450 Lake Rd, Apt 148013, Huntsville, Tx 77340 | 
|                3 |        3 | 1300 Garland Rd, Apt 12, Dallas, Tx 77111      | 
+------------------+----------+------------------------------------------------+
2 rows in set (0.00 sec)
Retrieved from "http://www.w3cyberlearnings.com/index.php?title=MySQL_Triggers&oldid=6105"
Navigation
Web
SQL
MISC
References
Personal tools
Namespaces
Variants
Actions