Mysql trigger example

Why use a trigger?

 

Advantages of triggers

 

1. Trigger's "automatic"

For programmers, the trigger is invisible, but he does. If no trigger is needed, when you update the name field of the user table, you need to write code to update redundant fields in other tables. For example, if there are redundant fields in a table, do you need to write a lot of code, does it look uncomfortable.

2. Data integrity of the trigger

The trigger has rollback. For example, I found that I like to update the data of five tables, but no two tables are updated, the other three tables are not updated.

However, if you use php code to write data, this may happen. For example, if you update the data of two tables, the database crashes. You are depressed, some are updated, and some are not updated. In this way, the page is displayed as inconsistent, and the bug changes.

 

Trigger example

 

Trigger: monitors a situation and triggers an operation.

Four elements of trigger creation syntax: 1. monitoring location (table) 2. monitoring event (insert/update/delete) 3. trigger time (after/before) 4. trigger event (insert/update/delete)

Syntax:

Create trigger triggerName after/before insert/update/delete on table name for each row # This statement is a fixed begin SQL statement in mysql; end;

 

Note: Each color corresponds to the preceding four elements.

First, create two tables:

# Product table create table g (id int primary key auto_increment, name varchar (20), num int); # Order table create table o (oid int primary key auto_increment, gid int, much int); insert into g (name, num) values ('item 1', 10), ('item 2', 10), ('item 3', 10 );

 

If we do not use the trigger before: Suppose we have sold 3 products 1, we need to do two things.

1. insert a record into the order table insert into o (gid, much) values (); 2. update Item 1 remaining quantity update g set num = num-3 where id = 1;

 

Now, create a trigger:

Execute this statement first: delimiter $ (meaning that the end of the mysql statement is changed to end with $)

Create trigger tg1after insert on ofor each row beginupdate g set num = num-3 where id = 1; end $ at this time we just need to execute: insert into o (gid, much) values (1, 3) $

 

The number of item 1 Changes to 7, which means that when we insert an order, the trigger will automatically update the order.

 

But now there is a problem, because the num and id in the trigger are all dead, so no matter which product we buy, the number of item 1 will be updated in the end. For example, we insert another record into the order table: insert into o (gid, much) values (2, 3). After execution, we will find that the number of item 1 has changed to 4, the number of product 2 has not changed. This is obviously not the result we want. We need to modify the previously created trigger.

How can we reference the value of a row in the trigger, that is, we need to obtain the value of gid or much in the newly inserted order record.

For insert, the new row is represented by new, and the value of each column in the row is represented by the new. Column name.

So now we can change our triggers like this.

Create trigger tg2after insert on ofor each row beginupdate g set num = num-new.much where id = new. gid; (note that this is different from the first trigger) end $

 

After the second trigger is created, delete the first trigger.

Drop trigger tg1 $

Next, let's test and insert an order record: insert into o (gid, much) values (2, 3) $

After the execution, the number of product 2 Changes to 7, and now it is correct.

 

There are two cases:

1. When a user cancels an order, we directly delete it. Do we need to add the corresponding quantity of goods?

2. How do I write a trigger change when the user modifies the number of orders?

First, let's analyze the first situation:

Monitored location: o table

Monitoring event: delete

Trigger Time: after

Trigger Event: update

For delete: a row is deleted later. To reference the row to be deleted, use old. The column name can reference the value of the row to be deleted.

Then we should write the trigger like this:

Create trigger tg3 after delete on o for each row begin update g set num = num + old. much where id = old. gid; (pay attention to changes here) end $

 

The creation is complete.

Then execute delete from o where oid = 2 $

The number of product 2 is changed to 10 again.

 

Case 2:

Monitored location: o table

Monitoring event: update

Trigger Time: after

Trigger Event: update

For update: The modified row and the data before modification are represented by old. The old column name reference is the value in the row before modification;

The modified data is represented by new. The new. Column name reference is the value in the row after the modification.

Then we should write the trigger like this:

Create trigger tg4 after update on o for each row begin update g set num = num + old. Much-new.much where id = old/new. gid; end $

 

Restore the old quantity first, and then subtract the new quantity, which is the modified quantity.

Let's test: clear the data in both the product table and order table, which is easy to test.

Suppose we insert three commodities into the commodity table, and the quantity is 10,

Buy 3 items 1: insert into o (gid, much) values (1, 3) $

At this time, the number of item 1 becomes 7;

Modify the inserted order record: update o set much = 5 where oid = 1 $

When we purchase 5 items 1, then we can query the item table and find that there are only 5 items left in the number of item 1, which indicates that our trigger has played a role.

If you can't see it again, let's proceed.

In MySQL Server, a certain operation on a table triggers certain conditions (Insert, Update, Delete, etc.) to automatically execute a program. In this sense, triggers are a special stored procedure. The following describes how to use the MySQL trigger instance to understand the trigger process!

1. Create a MySQL instance data table:

Create two tables t_a and t_ B in the default test database of mysql:

Download: Create_ SQL. SQL

/* Table structure for table 't_ A' */drop table if exists 't_ a'; create table 't_ A' ('id' smallint (1) unsigned not null AUTO_INCREMENT, 'username' varchar (20) default null, 'groupid' mediumint (8) unsigned not null default '0', primary key ('id ')) ENGINE = MyISAM AUTO_INCREMENT = 16 default charset = latin1;/* Data for the table 't_ A' */lock tables 't_a' WRITE; unlock tables; /* Table structure for table 't_ B '*/drop table if exists 't_ B'; create table 't_ B '('id' smallint (1) unsigned not null AUTO_INCREMENT, 'username' varchar (20) default null, 'groupid' mediumint (8) unsigned not null default '0', primary key ('id ')) ENGINE = MyISAM AUTO_INCREMENT = 57 default charset = latin1;/* Data for the table 't_ B '*/lock tables 't_ B' WRITE; UNLOCK TABLES;

Create a CUD (add, modify, or delete) trigger in table t_a to synchronize table data of t_a with table t_ B to implement CUD, note that there is only one corresponding trigger for the same type of event in each table when creating a trigger. Why can only one trigger be ignored? Please refer to the MYSQL help documentation.

II. Create a MySQL instance trigger:

Create trigger tr_a_insert, tr_a_update, and tr_a_delete in sequence on instance data table t_a.

1. Create an INSERT trigger trigger_a_insert:

Download: trigger_a_insert. SQL

DELIMITER $ USE 'test' $ -- determines whether the tr_a_insert TRIGGER exists in the database drop trigger /*! 50032 if exists */'Tr _ a_insert '$ -- the tr_a_insert Trigger does not exist. CREATE a Trigger. -- Trigger triggers the CREATE/* Trigger after successful insert /*! 50017 DEFINER = 'root' @ 'localhost' */TRIGGER 'tr _ a_insert' after insert on't _ A' for each row begin -- AFTER Trigger is triggered, insert into't _ B 'SET username = NEW. username, groupid = NEW. groupid; END; $ DELIMITER;

2. Create an UPDATE trigger trigger_a_update:

Download: trigger_a_update. SQL

DELIMITER $ USE 'test' $ -- determines whether a tr_a_update TRIGGER exists in the database drop trigger /*! 50032 if exists */'Tr _ a_update '$ -- IF the tr_a_update Trigger does not exist, CREATE a Trigger. -- Trigger triggers the Trigger after successful update /*! 50017 DEFINER = 'root' @ 'localhost' */TRIGGER 'tr _ a_update' after update on't _ A' for each row begin -- Trigger, when the t_a table groupid, when the username data is changed, an updated data IF new is synchronized to table t_ B. groupid! = Old. groupid OR old. username! = New. username then update 't_ B 'SET groupid = NEW. groupid, username = NEW. username WHEREusername = OLD. username AND groupid = OLD. groupid; end if; END; $ DELIMITER;

3. Create the DELETE trigger trigger_a_delete:

Download: trigger_a_delete. SQL

DELIMITER $ USE 'test' $ -- determines whether the tr_a_delete TRIGGER exists in the database drop trigger /*! 50032 if exists */'Tr _ a_delete '$ -- the tr_a_delete Trigger does not exist. CREATE a Trigger. -- Trigger triggers CREATE/* when the Trigger condition is delete /*! 50017 DEFINER = 'root' @ 'localhost' */TRIGGER 'tr _ a_delete 'after delete on't _ A' for each row begin -- AFTER the t_a table data is deleted, delete from 't_ B 'WHERE username = Old. username AND groupid = OLD. groupid; END; $ DELIMITER;

III. Test the MySQL instance trigger:

 

Test and implement three Triggers: t_a and t_ B for data synchronization CUD (add, modify, and delete).

1. Test the MySQL instance tr_a_insert trigger:

Add a new piece of data in table t_a, and then query whether the data in table t_a/t_ B is synchronized. The test trigger is successful. No matter under what circumstances, when one or more record sets are added, no data insert operation is performed on table t_ B, and multiple record sets are added at the same time.

Run the following MySQL trigger instance test:

-- Insert into 't_ A' (username, groupid) VALUES ('sky54. net ', 123) -- query t_a table SELECT id, username, groupid FROM 't_a' -- query t_ B table SELECT id, username, groupid FROM 't_ B'

2. Test MySQL instance tr_a_update and tr_a_delete triggers:

 

The test principles and steps of these two MySQL triggers are the same as those of the tr_a_insert trigger. First, modify/delete a piece of data and then view the data changes in table t_a and table t_ B respectively, data Change synchronization indicates that the Trigger instance is successful. Otherwise, you need to troubleshoot the error gradually.

Everything in the world has its own advantages and disadvantages. Its advantages and disadvantages are its own relative aspects. Of course, here we do not emphasize "The world is not black or white", "the existence is reasonable. Of course, the advantages of MySQL triggers are not mentioned. To address the shortcomings, MySQL triggers do not have a good debugging and management environment and are difficult to test in various system environments. Testing is more difficult than MySQL stored procedures, therefore, we recommend that you use stored procedures instead of MySQL triggers in the production environment.