测试表与 trigger 创建, mid_test 中 sumflag 的值为 base_table 中 isflag 大于 0 的值
- MySQL> create table base_test(id int auto_increment primary key,
- -> isflag tinyint(1),
- -> ownflag varchar(5))engine=innodb;
- Query OK, 0 rows affected (0.45 sec)
- MySQL> create table mid_test(ownflag varchar(5) primary key,
- -> sumflag int)engine=innodb;
- Query OK, 0 rows affected (0.11 sec)
表创建
- DELIMITER $
- create trigger tri_base_insert after insert
- on base_test for each row
- begin
- if new.isflag> 0 then
- declare c int;
- set c = (select sumflag from mid_test where ownflag = new.ownflag);
- update mid_test set sumflag = c + 1 where ownflag = new.ownflag;
- end if;
- end$
- DELIMITER ;
Insert trigger 创建
- DELIMITER $
- create trigger tri_base_update after update
- on base_test for each row
- begin
- declare c int;
- declare d int;
- if new.isflag <> old.isflag then
- if new.isflag> 0 then set d=1;
- else set d=-1;
- end if;
- set c = (select sumflag from mid_test where ownflag = new.ownflag);
- update mid_test set sumflag = c + d where ownflag = new.ownflag;
- end if;
- end$
- DELIMITER ;
Update trigger 创建
- INSERT INTO `wzy`.`mid_test` (`ownflag`, `sumflag`) VALUES ('1', '0');
- INSERT INTO `wzy`.`mid_test` (`ownflag`, `sumflag`) VALUES ('2', '0');
- INSERT INTO `wzy`.`mid_test` (`ownflag`, `sumflag`) VALUES ('3', '0');
- INSERT INTO `wzy`.`mid_test` (`ownflag`, `sumflag`) VALUES ('4', '0');
Mid_test 初始化
测试:
- MySQL> insert into base_test values(null,1,1);
- Query OK, 1 row affected (0.00 sec)
- MySQL> select * from mid_test where ownflag='1';
- +---------+---------+
- | ownflag | sumflag |
- +---------+---------+
- | 1 | 1 |
- +---------+---------+
- 1 row in set (0.00 sec)
- MySQL> insert into base_test values(null,1,1);
- Query OK, 1 row affected (0.12 sec)
- MySQL> select * from mid_test where ownflag='1';
- +---------+---------+
- | ownflag | sumflag |
- +---------+---------+
- | 1 | 2 |
- +---------+---------+
- 1 row in set (0.00 sec)
- MySQL> insert into base_test values(null,1,1);
- Query OK, 1 row affected (12.34 sec)
- MySQL> select * from mid_test where ownflag='1';
- +---------+---------+
- | ownflag | sumflag |
- +---------+---------+
- | 1 | 3 |
- +---------+---------+
- 1 row in set (0.00 sec)
- MySQL> update base_test set isflag=0 where ownflag='1';
- Query OK, 0 rows affected (0.00 sec)
- Rows matched: 3 Changed: 0 Warnings: 0
- MySQL> select * from mid_test where ownflag='1';
- +---------+---------+
- | ownflag | sumflag |
- +---------+---------+
- | 1 | 0 |
- +---------+---------+
- 1 row in set (0.00 sec)
- MySQL> update base_test set isflag=1 where id=3;
- Query OK, 1 row affected (0.04 sec)
- Rows matched: 1 Changed: 1 Warnings: 0
- MySQL> select * from mid_test where ownflag='1';
- +---------+---------+
- | ownflag | sumflag |
- +---------+---------+
- | 1 | 1 |
- +---------+---------+
- 1 row in set (0.05 sec)
来源: http://www.linuxidc.com/Linux/2018-09/154357.htm