为了账号安全,请及时绑定邮箱和手机立即绑定

MySQL 触发器实现

标签:
MySQL

Summary: in this tutorial, you will learn about MySQL triggers implementation. In addition, we will show you how MySQL stores trigger definitions and the limitations of triggers in MySQL.

Introduction to MySQL triggers

In MySQL, a trigger is a set of SQL statements that is invoked automatically when a change is made to the data on the associated table. A trigger can be defined to be invoked either before or after the data is changed by INSERTUPDATE or DELETE statements. MySQL allows you to define maximum six triggers for each table.

  • BEFORE INSERT – activated before data is inserted into the table.

  • AFTER INSERT- activated after data is inserted into the table.

  • BEFORE UPDATE – activated before data in the table is updated.

  • AFTER UPDATE - activated after data in the table is updated.

  • BEFORE DELETE – activated before data is removed from the table.

  • AFTER DELETE – activated after data is removed from the table.

When you use a statement that makes change to the table but does not use INSERTDELETE or UPDATE statement, the trigger is not invoked. For example, the TRUNCATE statement removes the whole data of a table but does not invoke the trigger associated with that table.

There are some statements that use the INSERT statement behind the scenes such as REPLACE statement and LOAD DATA statement. If you use these statements, the corresponding triggers associated with the tables if available will be invoked.

Triggers defined for a table must have a unique name. You can have the same trigger name that defines for different tables but it is not recommended. In practice, the names of triggers follow the following naming convention:

(BEFORE | AFTER)_tableName_(INSERT| UPDATE | DELETE)

 

MySQL Triggers Storage

MySQL stores triggers in a data directory e.g., /data/classicmodels/ with the files named tablename.TRG and triggername.TRN:

  • The tablename.TRG file maps the trigger to the corresponding table.

  • the triggername.TRN file contains the trigger definition.

You can back up the MySQL triggers by copying the trigger files to the backup folder. You can also backup the triggers using the mysqldump tool.

MySQL Trigger Limitations

MySQL triggers have all features in standard SQL however there are some limitations that you should know before using them in your applications.

MySQL triggers cannot:

  • Use SHOWLOAD DATALOAD TABLEBACKUP DATABASE, RESTOREFLUSH and RETURN statements.

  • Use statements that commit or rollback implicitly or explicitly such as COMMITROLLBACKSTART TRANSACTIONLOCK/UNLOCK TABLESALTERCREATEDROPRENAME, etc.

  • Use prepared statements such as PREPAREEXECUTE, etc.

  • Use dynamic SQL statements.

  • Call a stored procedure or stored function.

In this tutorial, we have shown you how triggers are implemented in MySQL. We also discussed about trigger’s storage as well as trigger’s limitations in MySQL.

Related Tutorials

原文链接:http://outofmemory.cn/mysql/trigger/mysql-trigger-implementation

点击查看更多内容
TA 点赞

若觉得本文不错,就分享一下吧!

评论

作者其他优质文章

正在加载中
  • 推荐
  • 评论
  • 收藏
  • 共同学习,写下你的评论
感谢您的支持,我会继续努力的~
扫码打赏,你说多少就多少
赞赏金额会直接到老师账户
支付方式
打开微信扫一扫,即可进行扫码打赏哦
今天注册有机会得

100积分直接送

付费专栏免费学

大额优惠券免费领

立即参与 放弃机会
意见反馈 帮助中心 APP下载
官方微信

举报

0/150
提交
取消