Triggers—MySQL5.6and5.7_MySQL

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Triggers—MySQL5.6and5.7_MySQL，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含2761字，纯文字阅读大概需要4分钟。

内容图文

MySQL Triggers are changing in 5.7 in a big way. Triggers have been around since 5.0 and have not changed much up to 5.6 but will gain the ability to have multiple triggers on the same event. Previously you had ONE trigger maximum on a BEFORE UPDATE, for example, and now you can have multiple triggersandset their order.

So what is a trigger? Triggers run eitherBEFOREorAFTERanUPDATE,DELETE, orINSERTis performed. You also get access to theOLD.col_nameandNEW.col_namevariables for the previous value and the newer value of the column.

So how do you use a trigger? Let say you are updating the price of an inventory item in a product database with a simple UPDATE statement. But you also want to track when the price change and the old price.

The table for products.
CREATE TABLE products (id INT NOT NULL auto_increment,
price DECIMAL(5,2) NOT NULL,
PRIMARY KEY (id));

The table for price changes on the product table.
CREATE TABLE products_log (id INT NOT NULL,
price DECIMAL(5,2) NOT NULL,
change_date timestamp);

Now to define a trigger that will log price changes. We do this when a price is updated. Now the use od OLD.price to avoid confusion between the old price or the new price being saved in the log.
DELIMITER |
CREATE TRIGGER product_price_logger
BEFORE UPDATE ON products
FOR EACH row
BEGIN
INSERT INTO products_log (id, price)
VALUES (id, OLD.PRICE);
END
|
DELIMITER ;

Add in some data.
INSERT INTO products (price) VALUES (1.10),(2.24),(.99),(.01),(.34);

So UPDATE a record.
UPDATE products SET price='1.11' WHERE ID = 1;

So did it work? Yes, and no. RunningSELECT * FROM products_log;Provides us with a time stamp of the change and the OLD.price. But I forgot to also record the id!!Challenge: Correct my mistake and compare it to an update I will make in a few days.

Now 5.7 introduces multiple triggers for the same event. Lets add yet another log this time recording who made the change;

The ‘who made the change table’.
CREATE table who_changed (
id INT NOT NULL,
who_did_it CHAR(30) NOT NULL,
when_did_it TIMESTAMP);

And the second trigger.
DELIMITER |
CREATE TRIGGER product_price_whom
BEFORE UPDATE ON products
FOR EACH ROW
FOLLOWS product_price_logger
BEGIN
INSERT INTO who_changed (id, who_did_it)
VALUES (OLD.id, user());
END
|
DELIMITER ;

SoUPDATE products SET price='19.99' WHERE id=4;is run and we see that both triggers execute. Note thatSHOW TRIGGERS fromschema;does not provide any information on trigger order. But you can find all that asaction_orderinPERFORMANCE_SCHEMA.TRIGGERS

Being able to order triggers makes it easy to make logical steps when processing data. Can you get into trouble with this? I am certain someone will manage to make a mess with this. But I think most of us will enjoy being able to use this great new functionality.

内容总结

以上是互联网集市为您收集整理的Triggers—MySQL5.6and5.7_MySQL全部内容，希望文章能够帮你解决Triggers—MySQL5.6and5.7_MySQL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---