MySQL trigger details

MySQL trigger details

Trigger-trigeer is a function used in a database to monitor data changes and trigger certain operations, simply put, a stored procedure is executed before or after a table meets certain conditions. Here we will briefly talk about how to use triggers in MySQL. MySQL triggers can be used to check data integrity, capture business logic errors at the database layer, and make changes to data in audit tables, however, its disadvantage is that it can only provide extended verification after the trigger is used, and cannot replace all the verifications. Second, it increases the maintenance cost, finally, in MySQL, triggers increase the server overhead. We recommend that you do not use a large number of triggers in tables with frequent businesses. In MySQL, the four elements of the trigger creation syntax are as follows: 1. monitoring objects (mostly tables) 2. monitoring event (insert/update/delete) 3. trigger time (after/before) 4. trigger event (insert/update/delete ).

First, when using a trigger, MySQL currently performs insert, delete, and update operations on a table before or after the condition to trigger pre-defined operations. Note that when using a MySQL trigger, you must have at least the permissions of the super User, by default, MySQL triggers can be operated only by ordinary users like functions. To grant normal users the permission to create triggers, you must enable the log_bin_trust_function_creators variable:

mysql>  SET  GLOBAL  log_bin_trust_function_creators =  ON ; # Add it to my. cnf or my. ini after Configuration Query OK, 0  rows  affected (0.00 sec)

After this parameter is enabled, common users can also create triggers and functions. Secondly, the current MySQL triggers only support DML and do not support DDL. The common DDL syntax for creation is as follows:

CREATE  TRIGGER  Trigger name AFTER /BEFORE  INSERT / UPDATE / DELETE  ON  Table Name FOR  Each row # This statement is fixed in MYSQL.  BEGIN DML statement; END ;

In addition, pay attention to the difference between old and new under the MySQL trigger. The following uses a text on the network:

1. When the insert statement is used, if there is no data in the original table, the newly inserted data is new for the table after the data is inserted.

2. When the delete statement is used, the deleted data is old compared to the data in the deleted table.

3. When the update statement is used, when the original table data is modified, the data modified in the original table is old, the modified data in the table is new.

So there is a difference between using new and old in triggers. Here we use two tables to describe that there are two tables tbl_name and tbl_name_log.

DDL of tbl_name:

DROP  TABLE  IF EXISTS `tbl_name`; CREATE  TABLE  `tbl_name` (   `id`  int (10)  NOT  NULL  COMMENT  'Name id' ,   ` name `  varchar (50)  NOT  NULL  COMMENT  'Name' ,   PRIMARY  KEY  (`id`) ) ENGINE=InnoDB  DEFAULT  CHARSET=utf8;

Tbl_name_log DDL:

DROP  TABLE  IF EXISTS `tbl_name_log`; CREATE  TABLE  `tbl_name_log` (   `id`  int (10)  NOT  NULL  AUTO_INCREMENT COMMENT  'Audit id' ,   ` action `  varchar (10)  NOT  NULL  COMMENT  'Actions performed in the tbl _ name table' ,   ` name `  varchar (50)  NOT  NULL  COMMENT  'Operation name' ,   ` time ` datetime  DEFAULT  NULL  ON  UPDATE  CURRENT_TIMESTAMP  COMMENT  'Operation time' ,   PRIMARY  KEY  (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1  DEFAULT  CHARSET=utf8;

In tbl_name, you can add three triggers in tbl_name after recording the insert/update/delete events in the tbl_name_log table:

DROP  TRIGGER  IF EXISTS `audit_name_add`; DELIMITER ;; CREATE  TRIGGER  `audit_name_add`  AFTER  INSERT  ON  `tbl_name`  FOR  Each row # Trigger for logging tbl_name_log after tbl_name is inserted BEGIN   INSERT  INTO  `tbl_name_log` (` action `,` name `,` time `) VALUES   ( 'add' ,new.` name `,NOW()); END ;; DELIMITER ; DROP  TRIGGER  IF EXISTS `audit_name_update`; DELIMITER ;; CREATE  TRIGGER  `audit_name_update`  AFTER  UPDATE  ON  `tbl_name`  FOR  Each row # Trigger for logging tbl_name_log after tbl_name is updated BEGIN   INSERT  INTO  `tbl_name_log` (` action `,` name `,` time `) VALUES   ( 'update' ,new.` name `,NOW()); END ;; DELIMITER ; DROP  TRIGGER  IF EXISTS `audit_name_del`; DELIMITER ;; CREATE  TRIGGER  `audit_name_del`  AFTER  DELETE  ON  `tbl_name`  FOR  Each row # Trigger for logging in tbl_name_log after tbl_name is deleted BEGIN   INSERT  INTO  `tbl_name_log` (` action `,` name `,` time `) VALUES   ( 'del' ,old.` name `,NOW()); END ;; DELIMITER ;

To test whether the table is inserted, updated, or deleted in the tbl_name table, corresponding records are stored in the tbl_name_log table.