web stats
Mysql Basic Trigger Examples | Why Cloud Computing

Mysql Basic Trigger Examples

Mysql triggers come in really handy when you have some other tables to be modified when a particular table in the database is changed. In this article I am posting a very basic example for mysql triggers along with sufficient explanation. After reading this if you still have any questions, don’t hesitate to drop your comments.

Share our stuff and help us grow. Need help to create something new or fix something old? Contact us to know if we can help ūüôā

Before we start, let’s create a test database called “t1” and create two tables called “example” and “example1”. Both the tables will have id and data¬†fields.

create database t1;

use t1;

CREATE TABLE example (id INT, data VARCHAR(100));
CREATE TABLE example1 (id INT, data VARCHAR(100));

Now, here is a simple trigger which will update the table “example1” on inserting values into “example” table. Check out the explanation below the trigger example.

DELIMITER &&
CREATE TRIGGER `t1`.`after_insert_example` AFTER INSERT ON `example` FOR EACH ROW

BEGIN INSERT INTO example1 (id, data) VALUES (NEW.id, NEW.data); END&&

DELIMITER ;

* The first line, DELIMITER && says to change the default delimiter from ; to &&.

* Second line creates a trigger for the database t1 and after_insert_example is the name of the trigger. The trigger is applied for every insert on example table.

* Third line says to insert the new.id and new.data (which are the data input given for example table) into the example1 table. END&& says that its the corresponding end to the BEGIN and to the trigger.

* Fourth line says to return the default delimiter of mysql back to ;

Now, insert some values into “example” table and see if we can see it on the other table “example1”.

INSERT INTO example values (‘2’, ‘trigger_in’);

mysql> select * from example;
+——+————+
| id | data |
+——+————+
| 2 | trigger_in |
+——+————+
1 row in set (0.00 sec)

mysql> select * from example1;
+——+————+
| id | data |
+——+————+
| 2 | trigger_in |
+——+————+
1 row in set (0.00 sec)

In the above mysql statements, we have inserted sample values to example table and we see the values in both example and example1 tables which means that our trigger has successfully executed.

Here is a reference¬†article that will give you few more details about triggers. Now, let’s try something different.¬†Of course, its a slightly complex example that the one mentioned above.

DELIMITER &&

CREATE TRIGGER `t1`.`after_insert_onset_data_feild`

AFTER INSERT ON `example` FOR EACH ROW

BEGIN

IF NEW.data = ‘copy’ THEN

INSERT INTO example1 (id, data) VALUES (NEW.id, NEW.data);

END IF;

END&&

DELIMITER ;

The above trigger has similar statements to the previous trigger. So, I am skipping the few things that are common to both triggers. The above trigger says, that the data should be replicated into example1 table only if the data field in the new entry to table example is equal to copy. This is done using the IF statement starts from line 5 and ends at line 7.

mysql> insert into example values (‘3’, ‘copy’);
Query OK, 1 row affected (0.00 sec)

mysql> select * from example;
+——+————+
| id | data |
+——+————+
| 3 | copy |
| 2 | trigger_in |
+——+————+
2 rows in set (0.00 sec)

mysql> select * from example1;
+——+————+
| id | data |
+——+————+
| 2 | trigger_in |
| 3 | copy |
+——+————+
2 rows in set (0.00 sec)

mysql> insert into example values (‘3’, ‘nocopy’);
Query OK, 1 row affected (0.00 sec)

mysql> select * from example;
+——+————+
| id | data |
+——+————+
| 3 | copy |
| 2 | trigger_in |
| 3 | nocopy |
+——+————+
3 rows in set (0.00 sec)

mysql> select * from example1;
+——+————+
| id | data |
+——+————+
| 2 | trigger_in |
| 3 | copy |
+——+————+
2 rows in set (0.00 sec)

In the first insert statement,  data field is set to copy and an insert is made to example table, so the trigger worked and the data is replicated to example1. In the second insert statement, data field is set to nocopy and an insert is made to example table and no replication took place to example1 table.

Share our stuff and help us grow. Need help to create something new or fix something old? Contact us to know if we can help ūüôā

Related Posts:

One Response to Mysql Basic Trigger Examples

  1. Maran says:

    I can’t create two trigger for example table in mysql but works perfectly in separate, does my version didn’t support.

Leave a Reply

Your email address will not be published. Required fields are marked *

Why ask?