A simple example of a trigger in MySQL

A simple example

1.1. Create a table:

CREATE TABLE T (S1 integer);

1.2. Triggers:

The code is as follows	Copy Code
? delimiter | Create trigger T_trigger before insert on t for each row Begin Set @x = "Hello trigger"; Set new.s1 = 55; End

|
1.3. If the trigger creation error, may only be deleted, at least I tried not to replace

Drop trigger T_trigger;

1.4. When the insert is executed:

INSERT into T values (1);

1.5. Triggers are executed T_trigger

Select @x,t.* from T;

1.6. You can see the results:

1.7 can use show triggers; To view a newly created trigger

#创建触发器, when you add a record to the order table, update the goods table

The code is as follows	Copy Code
Delimiter $ CREATE TRIGGER Trigger1 After INSERT on ' order ' For each ROW BEGIN UPDATE goods SET Num=num-new.much WHERE id=new.gid; end$

Perform

The code is as follows	Copy Code
INSERT into ' order ' (Gid,much) VALUES (1,5) After SELECT * FROM goods WHERE id=1

Found the number of televisions left 30

When performing

The code is as follows	Copy Code
INSERT into ' order ' (Gid,much) VALUES (2,100)

After
Found
The number of refrigerators remains-77

This is a very obvious loophole, how to remedy it?
Since the update event occurred after the insert, we cannot advance the order quantity of the user (that is, the Orders table
Much of the field) for filtering
Solution:
When the trigger is created, the After keyword is changed to before and the user's order quantity is judged

First, you have to delete the first trigger.
Drop trigger Trigger1;

The code is as follows	Copy Code
#创建触发器 #触发时间: Before Delimiter $ CREATE TRIGGER Trigger1 Before INSERT on ' order ' For each ROW BEGIN IF New.much >5 THEN SET new.much=5 End IF; UPDATE goods SET Num=num-new.much WHERE id=new.gid; end$

In this way, when the insert INTO ' order ' (Gid,much) VALUES (2,100) is executed, the number of orders written to the form is actually only 5, and the number of inventory in the goods table is reduced by 5, as the first trigger before the insert operation
Update operation, you can make a judgment on the order quantity

Now let's do a question.

Now there is a table user (Id,name,password)

There is also a table user_data (, D_id,d_name,d_tel);

I want to trigger the User_data to add a record to the user table when you add a record.

Like the record is 1, John, 114.

How to write this trigger in MySQL, remember that MySQL is not another database

The code is as follows	Copy Code
Mysql> delimiter// -> CREATE TRIGGER Sitedata_ins2 -> before insert on user -> for each row -> Begin -> If NOT EXISTS (select 1 from User_data where d_id=new.id) then -> INSERT INTO User_data (d_id,d_name) values (new.id,new.name); -> End IF; -> end;// -> delimiter;

Or a little simpler:

The code is as follows	Copy Code
Mysql> delimiter// -> CREATE TRIGGER Sitedata_ins2 -> before insert on user -> for each row -> Begin -> INSERT INTO User_data (d_id,d_name) values (new.id,new.name); -> end;// -> delimiter;

Two methods are available, we recommend that you use the first type