1. Difference between Before and After in Trigger
1) After we perform one action, the trigger is invoked. But there is no machanism to check if the action we perform is reasonable.
# Bootstrap data select * from goods; +----------+------------+--------------+ | goods_id | goods_name | goods_amount | +----------+------------+--------------+ | 1 | ASUS | 13 | | 2 | ThinkPad | 10 | +----------+------------+--------------+ select * from order_table; +----------+----------+-------------+ | order_id | goods_id | order_count | +----------+----------+-------------+ | 2 | 1 | 1 | | 3 | 1 | 9 | +----------+----------+-------------+ # Bootstrap trigger # 1. If we place one order item, goods amount reduce. # 2. If we delete one order item, goods amount increase. # 3. If we modify one order item, goods amount modify. # What if we place an order like below insert into order_table(goods_id, order_count) values(1, 20); select * from order_table; +----------+----------+-------------+ | order_id | goods_id | order_count | +----------+----------+-------------+ | 2 | 1 | 1 | | 3 | 1 | 9 | | 4 | 1 | 20 | +----------+----------+-------------+ select * from goods; +----------+------------+--------------+ | goods_id | goods_name | goods_amount | +----------+------------+--------------+ | 1 | ASUS | -7 | | 2 | ThinkPad | 10 | +----------+------------+--------------+ # The result is not reasonable! delete from order_table where order_id = 4; select * from goods; +----------+------------+--------------+ | goods_id | goods_name | goods_amount | +----------+------------+--------------+ | 1 | ASUS | 13 | | 2 | ThinkPad | 10 | +----------+------------+--------------+ select * from order_table; +----------+----------+-------------+ | order_id | goods_id | order_count | +----------+----------+-------------+ | 2 | 1 | 1 | | 3 | 1 | 9 | +----------+----------+-------------+ # There is lack of checking mechanism to validate whether the order item is meaningful. # So we can use Before to validate
2) After: Once the operation that triggered this trigger, then there is no way back. The operation in trigger have to be done. The trigger is triggered after CUD works.
3) Before: Once the operation that triggered this trigger, we can make some validate/check and revocate the operation. The trigger is triggered before CUD works.
# For the order item, if order_count > 5, we suppose this order item is exception and we should modify order_count to 5; # Four essential elements for creating Trigger # Scope: Table order_table # Trigger Event: Insert # When: Before # Triggered Event: Update drop trigger add_order; delimiter $ create trigger add_order before insert on order_table for each row begin if new.order_count > 5 then set new.order_count = 5; end if; update goods set goods_amount = goods_amount - new.order_count where goods_id = new.goods_id; end$ delimiter ; # Validate if the trigger works correctly insert into order_table(goods_id, order_count) values(1, 10); select * from order_table; +----------+----------+-------------+ | order_id | goods_id | order_count | +----------+----------+-------------+ | 2 | 1 | 1 | | 3 | 1 | 9 | | 5 | 1 | 5 | +----------+----------+-------------+ select * from goods; +----------+------------+--------------+ | goods_id | goods_name | goods_amount | +----------+------------+--------------+ | 1 | ASUS | 8 | | 2 | ThinkPad | 10 | +----------+------------+--------------+ # Bingo!
2. How to list all triggers?
# Show all triggers show triggers;
相关推荐
mysql安装配置教程 MySQL安装和配置的步骤,适用于Windows系统: Windows系统上安装MySQL: 下载MySQL:
MySQL :: MySQL yog
《高可用MySQL:构建健壮的数据中心》是“MySQL High Availability”的中文翻译版,主要讲解真实环境下如何使用MySQL的复制、集群和监控特性,揭示MySQL可靠性和高可用性的方方面面。《高可用MySQL:构建健壮的数据...
《高可用MySQL:构建健壮的数据中心》是“MySQL High Availability”的中文翻译版,主要讲解真实环境下如何使用MySQL的复制、集群和监控特性,揭示MySQL可靠性和高可用性的方方面面。《高可用MySQL:构建健壮的数据...
《高可用MySQL:构建健壮的数据中心》是“MySQL High Availability”的中文翻译版,主要讲解真实环境下如何使用MySQL的复制、集群和监控特性,揭示MySQL可靠性和高可用性的方方面面。《高可用MySQL:构建健壮的数据...
chown -R mysql:mysql /usr/local/mysql/data/ chown -R mysql:mysql /usr/local/mysql/lib/ chown -R mysql:mysql /usr/local/mysql/sock/ 启动:mysql服务 /usr/local/mysql/share/mysql/mysql.server start 进入...
前几天重装了mysql,装好之后navicat一直正常可以对mysql进行操作,但是今天要使用命令行进行操作时遇到-bash: mysql: command not found的问题。 总结一下解决办法: 这个问题的出现是两个原因 1 .bash_profile中...
教你使用MySQL:MySQL常用命令一览
《涂抹MySQL:跟着三思一步一步学MySQL》PDF版本下载
MySQL 版本号 高山: 3.7 MySQL的: mariaDB-10.1.28-r1 ...拉: docker pull quay.io/perriea/alpine-mysql:1.0 运行: docker run -d -p 3306:3306 --env-file .env quay.io/perriea/alpine-mysql:1.0
高可用mysql:目前最好用的文档。构建健壮数据中心必备用书。
Gain skills in MySQL’s fundamental features, including supported data types, database management syntax, triggers, views, stored routine syntax, and import/export capabilities Work with hundreds of...
java连接mysql的稳定驱动
DSN:mysql://root:root@127.0.0.1:3306/testdb?pool_min=10&pool_max=100&conn_ttl=10 ?后面的参数列表: pool_min:连接池最小值,默认10 pool_max:连接池最大值,默认100 inactive_connection_ttl:默认0秒,...
利用zabbix监控mysql,方便好用,不需要在被监控端安装或执行任何脚本
jsp+mysql:毕业设计动漫网站。内含项目代码,数据库脚本。