mysql 觸發器的學習1

標籤：使用   os   io   for   re   c   cti   ar   

mysql> #接下來學習觸發器的概念
mysql> #觸發器是由事件來觸發某個操作的，包括insert update delete語句
mysql> #觸發器的基本形式：
mysql> #create trigger 觸發器名 before|after 觸發事件 on 表名 for each row 執行語句
mysql> #如果需要執行多條語句則使用 begin …… end 不同語句之間用“；”分號分割
mysql> #所以使用多行執行語句時，一定要用到的是 delimiter 來設定新的mysql的結束符號
mysql> #查看觸發器的情況，可以使用show triggers 和 select * from information_schema.triggers
mysql> #刪除觸發器 drop trigger 觸發器名
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(10) not null unique primary key,
-> name varchar(20) not null,
-> function varchar(50) ,
-> company varchar(20) not null,
-> address varchar(50)
-> );
Query OK, 0 rows affected (0.20 sec)

mysql> create table operate (
-> op_id int(10) not null unique paimary key auto_increment,
-> op_type varchar(20) not null,
-> op_time time not null
-> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near ‘paimary key auto_increment,
op_type varchar(20) not null,
op_time time not null
‘ at line 2
mysql> create table operate (
-> op_id int(10) not null unique primary key auto_increment,
-> op_type varchar(20) 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(10) NOT NULL,
`name` varchar(20) NOT NULL,
`function` varchar(50) DEFAULT NULL,
`company` varchar(20) NOT NULL,
`address` varchar(50) 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,‘電腦‘,‘編程‘,‘IBM‘,‘北京市朝陽區‘),
-> (2,‘手機‘,‘打電話‘,‘小米‘,‘北京市海澱區‘),
-> (3,‘平板電腦‘,‘玩遊戲‘,‘蘋果‘,‘美國洛杉磯‘);
Query OK, 3 rows affected (0.26 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from product;
+----+----------+----------+---------+--------------+
| id | name | function | company | address |
+----+----------+----------+---------+--------------+
| 1 | 電腦 | 編程 | IBM | 北京市朝陽區 |
| 2 | 手機 | 打電話 | 小米 | 北京市海澱區 |
| 3 | 平板電腦 | 玩遊戲 | 蘋果 | 美國洛杉磯 |
+----+----------+----------+---------+--------------+
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=‘案頭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 | 案頭PC | 編程 | IBM | 北京市朝陽區 |
| 3 | 平板電腦 | 玩遊戲 | 蘋果 | 美國洛杉磯 |
+----+----------+----------+---------+--------------+
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 into 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 into 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 into 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 into 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,‘delete‘,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‘,‘止血‘,‘北京藥廠‘,‘北京市昌平區‘);
Query OK, 1 row affected (0.00 sec)

mysql> update product set address=‘天津市開發區‘ 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 | 案頭PC | 編程 | IBM | 北京市朝陽區 |
| 3 | 平板電腦 | 玩遊戲 | 蘋果 | 美國洛杉磯 |
+----+----------+----------+---------+--------------+
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 和 after
mysql> #before是先完成觸發，再增刪改
mysql> #after是先完成增刪改，再進行觸發
mysql> 另外還有 old 和 new的使用