mysql 触发器的学习1

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: root@localhost
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: root@localhost
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: root@localhost
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: root@localhost
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: root@localhost
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的使用

mysql 触发器的学习1,布布扣,bubuko.com