MySQL Flip-flop learning 1

Mysql> #接下来学习触发器的概念
Mysql> #触发器是由事件来触发某个操作的, including insert update DELETE statement
Mysql> #触发器的基本形式:
Mysql> #create trigger Trigger name Before|after trigger event on table name for each row execution statement
Mysql> #如果需要执行多条语句则使用 begin ... end different statements are separated by ";" Semicolons
Mysql> #所以使用多行执行语句时, be sure to use delimiter to set the new end symbol for MySQL
Mysql> #查看触发器的情况, you can use show triggers and select * FROM Information_schema.triggers
mysql> #删除触发器 drop TRIGGER trigger name
Mysql>
Mysql>
Mysql>
Mysql> #实际练习
mysql> use test;
No connection. Trying to reconnect ...
Connection Id:6
Current database: * * * NONE * * *

Database changed
Mysql> Show tables;
Empty Set (0.00 sec)

Mysql> CREATE TABLE Product (
-ID Int (TEN) not null unique primary key,
-Name varchar (a) NOT NULL,
-function varchar (50),
Company varchar (a) NOT NULL,
Address varchar (50)
);
Query OK, 0 rows affected (0.20 sec)

Mysql> CREATE TABLE Operate (
-op_id Int (TEN) not null unique Paimary key auto_increment,
-Op_type varchar (a) NOT NULL,
-Op_time time NOT NULL
);
Error 1064 (42000): You have a error in your SQL syntax; Check the manual, corresponds to your
MySQL Server version for the right syntax-use near ' paimary key auto_increment,
Op_type varchar () NOT NULL,
Op_time time NOT NULL
' At line 2
Mysql> CREATE TABLE Operate (
-op_id Int (TEN) not null unique primary key auto_increment,
-Op_type varchar (a) NOT NULL,
-Op_time time NOT NULL
);
Query OK, 0 rows affected (0.09 sec)

Mysql> Show CREATE TABLE product \g
1. Row ***************************
Table:product
Create table:create Table ' product ' (
' id ' int (ten) is not NULL,
' Name ' varchar (not NULL),
' function ' varchar (DEFAULT NULL),
' Company ' varchar (a) not NULL,
' Address ' varchar DEFAULT NULL,
PRIMARY KEY (' id '),
UNIQUE KEY ' id ' (' ID ')
) Engine=myisam DEFAULT charset=latin1
1 row in Set (0.00 sec)

Mysql> Show tables;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect ...
Connection Id:7
Current Database:test

+----------------+
| Tables_in_test |
+----------------+
| operate |
| Product |
+----------------+
2 rows in Set (0.01 sec)

Mysql> CREATE trigger P_b_insert before insert on product for each row
INSERT into operate values (NULL, ' Insert ', now ());
Query OK, 0 rows affected (0.13 sec)

Mysql> CREATE trigger P_af_update after update on product for each row
INSERT into operate values (NULL, ' Update ', now ());
Query OK, 0 rows affected (0.06 sec)

Mysql> CREATE trigger P_af_del after delete on product for each row
INSERT into operate values (NULL, ' delete ', now ());
Query OK, 0 rows affected (0.07 sec)

Mysql> INSERT INTO product values
(1, ' computer ', ' programming ', ' IBM ', ' Chaoyang District, Beijing '),
(2, ' phone ', ' call ', ' Xiaomi ', ' Beijing Haidian District '),
(3, ' tablet ', ' play games ', ' Apple ', ' Los Angeles ');
Query OK, 3 rows affected (0.26 sec)
Records:3 duplicates:0 warnings:0

Mysql> SELECT * from Product;
+----+----------+----------+---------+--------------+
| ID | name | function | Company | Address |
+----+----------+----------+---------+--------------+
| 1 | Computer | Programming | IBM | Chaoyang District, Beijing |
| 2 | Mobile phone | Call | Millet | Haidian District, Beijing |
| 3 | Tablet PC | Play Games | Apple | Los Angeles, USA |
+----+----------+----------+---------+--------------+
3 Rows in Set (0.00 sec)

Mysql> SELECT * from operate;
+-------+---------+----------+
| op_id | Op_type | Op_time |
+-------+---------+----------+
| 1 | Insert | 00:22:03 |
| 2 | Insert | 00:22:03 |
| 3 | Insert | 00:22:03 |
+-------+---------+----------+
3 Rows in Set (0.00 sec)

Mysql> Update product set name= ' desktop PC ' where id=1;
Query OK, 1 row affected (0.09 sec)
Rows matched:1 changed:1 warnings:0

Mysql> SELECT * from operate;
+-------+---------+----------+
| op_id | Op_type | Op_time |
+-------+---------+----------+
| 1 | Insert | 00:22:03 |
| 2 | Insert | 00:22:03 |
| 3 | Insert | 00:22:03 |
| 4 | Update | 00:23:33 |
+-------+---------+----------+
4 rows in Set (0.00 sec)

Mysql> Delete from product where id=2;
Query OK, 1 row Affected (0.00 sec)

Mysql> SELECT * from Product;
+----+----------+----------+---------+--------------+
| ID | name | function | Company | Address |
+----+----------+----------+---------+--------------+
| 1 | Desktop PC | Programming | IBM | Chaoyang District, Beijing |
| 3 | Tablet PC | Play Games | Apple | Los Angeles, USA |
+----+----------+----------+---------+--------------+
2 rows in Set (0.00 sec)

Mysql> SELECT * from operate;
+-------+---------+----------+
| op_id | Op_type | Op_time |
+-------+---------+----------+
| 1 | Insert | 00:22:03 |
| 2 | Insert | 00:22:03 |
| 3 | Insert | 00:22:03 |
| 4 | Update | 00:23:33 |
| 5 | Delete | 00:24:11 |
+-------+---------+----------+
5 rows in Set (0.00 sec)

Mysql> Show triggers \g
1. Row ***************************
Trigger:p_b_insert
Event:insert
Table:product
Statement:insert to operate values (NULL, ' Insert ', now ())
Timing:before
Created:null
Sql_mode:
Definer: [Email protected]
Character_set_client:latin1
Collation_connection:latin1_swedish_ci
Database Collation:latin1_swedish_ci
2. Row ***************************
Trigger:p_af_update
Event:update
Table:product
Statement:insert to operate values (NULL, ' Update ', Now ())
Timing:after
Created:null
Sql_mode:
Definer: [Email protected]
Character_set_client:latin1
Collation_connection:latin1_swedish_ci
Database Collation:latin1_swedish_ci
3. Row ***************************
Trigger:p_af_del
Event:delete
Table:product
Statement:insert to operate values (NULL, ' delete ', now ())
Timing:after
Created:null
Sql_mode:
Definer: [Email protected]
Character_set_client:latin1
Collation_connection:latin1_swedish_ci
Database Collation:latin1_swedish_ci
3 rows in Set (0.04 sec)

Mysql> Drop trigger P_b_insert;
Query OK, 0 rows affected (0.02 sec)

mysql> drop trigger p_af_update;
Query OK, 0 rows affected (0.02 sec)

Mysql> Show triggers \g
1. Row ***************************
Trigger:p_af_del
Event:delete
Table:product
Statement:insert to operate values (NULL, ' delete ', now ())
Timing:after
Created:null
Sql_mode:
Definer: [Email protected]
Character_set_client:latin1
Collation_connection:latin1_swedish_ci
Database Collation:latin1_swedish_ci
1 row in Set (0.01 sec)

Mysql> CREATE trigger P_af_insert after insert on product for each row
INSERT into operate values (NULL, ' Insert ', now ());
Query OK, 0 rows affected (0.02 sec)

Mysql> CREATE trigger p_b_update before update on product for each row
INSERT into operate values (NULL, ' Update ', now ());
Query OK, 0 rows affected (0.02 sec)

Mysql> CREATE trigger P_b_del before delete on product for each row
-insert into operate values (NULL, ' dele Te ', now ());
Query OK, 0 rows affected (0.02 sec)

Mysql> SELECT * from information_schema.triggers where trigger_name= ' P_b_del ' \g
*************************** 1. Row ***************************
Trigger_catalog:null
trigger_schema:test
Trigger_name:p_b_del
Event_manipulation:delete
Event_object_catalog:null
event_object_schema:test
Event_object_table: Product
action_order:0
Action_condition:null
Action_statement:insert into operate values (NULL, ' delete ', Now ())
Action_orientation:row
Action_timing:before
Action_reference_old_table:null
Action_ Reference_new_table:null
Action_reference_old_row:old
action_reference_new_row:new
Created:null
Sql_mode:
Definer: [email protected]
Character_set_client:latin1
collation_connection:latin1_ Swedish_ci
Database_collation:latin1_swedish_ci
1 row in Set (1.45 sec)

Mysql> INSERT into product values (2, ' CCC ', ' hemostasis ', ' Beijing Pharma ', ' Peking Changping District ');
Query OK, 1 row Affected (0.00 sec)

Mysql> Update product set address= ' Tianjin Development Zone ' where id=2;
Query OK, 1 row Affected (0.00 sec)
Rows matched:1 changed:1 warnings:0

Mysql> Delete from product where id=2;
Query OK, 1 row Affected (0.00 sec)

Mysql> SELECT * from Product;
+----+----------+----------+---------+--------------+
| ID | name | function | Company | Address |
+----+----------+----------+---------+--------------+
| 1 | Desktop PC | Programming | IBM | Chaoyang District, Beijing |
| 3 | Tablet PC | Play Games | Apple | Los Angeles, USA |
+----+----------+----------+---------+--------------+
2 rows in Set (0.00 sec)

Mysql> SELECT * from operate;
+-------+---------+----------+
| op_id | Op_type | Op_time |
+-------+---------+----------+
| 1 | Insert | 00:22:03 |
| 2 | Insert | 00:22:03 |
| 3 | Insert | 00:22:03 |
| 4 | Update | 00:23:33 |
| 5 | Delete | 00:24:11 |
| 6 | Insert | 00:34:36 |
| 7 | Update | 00:35:08 |
| 8 | Delete | 00:35:23 |
| 9 | Delete | 00:35:23 |
+-------+---------+----------+
9 Rows in Set (0.00 sec)

Mysql>
Mysql>
Mysql> #触发器的关键在于 before and after
Mysql> #before是先完成触发, and then increase the deletion
Mysql> #after是先完成增删改, then trigger.
Mysql> is also used by old and new