MySQL Flip-flop

Trigger-trigeer is a situation in which a database is used to monitor changes in data and triggers a function to perform an operation, simply by executing a stored procedure before or after a change to a table that matches a certain condition. Here's a quick way to talk about how to use triggers under MySQL. Triggers that use MySQL can check data integrity, capture errors in the business logic in the database layer, and be useful for changes that use data in an audit table, but the downside is that only extended validation can be provided after the trigger is used, and all validations cannot be replaced, followed by an increase in later maintenance costs. At the end of the current MySQL under the trigger on the server overhead is relatively large, it is recommended that in the business more frequent tables do not use a large number of triggers, in MySQL triggers to create four elements of syntax: 1. Monitoring objects (mostly against tables) 2. Monitoring Events (Insert/update/delete ) 3. Trigger Time (After/before) 4. Trigger Event (Insert/update/delete).

First of all, the current MySQL can trigger a predefined operation by using a trigger to insert, delete, update, or post-condition a table. It is important to note that in the use of MySQL triggers must have sufficient permissions to at least the super user's permission, under the default conditions, the MySQL trigger is the same as the normal user is unable to operate, If you need to give normal users permission to create triggers, you need to turn on the log_bin_trust_function_creators variable:

mysql> SET GLOBAL log_bin_trust_function_creators = on; #在配置好后在my. cnf or My.ini Add query OK, 0 rows Affected (0.00 sec)

After this parameter is turned on, ordinary users can also create triggers and function, and second, the current MySQL trigger only supports DML and does not support DDL, which creates common DDL syntax as follows:

CREATE TRIGGER Trigger name After/before insert/update/delete on table name for each ROW #这句话在MYSQL是固定的BEGINDML语句; END;

Also note the difference between old and new under the MySQL trigger, which refers to a section of text on the network:

1, when using the INSERT statement, if there is no data in the original table, then for the table after inserting data is the new data inserted

2. When the DELETE statement is used, the deleted data is old relative to the data in the table after the data is deleted

3, when using the UPDATE statement, when the original table data is modified relative to the data after modifying the data of the table, the original table modified the data is old, and modify the data after the table is modified the data is new

So there's a difference between using new and old in a trigger, and here, with 2 tables, there are 2 tables Tbl_name and Tbl_name_log

DDL for Tbl_name:

DROP TABLE IF EXISTS ' tbl_name '; CREATE TABLE ' tbl_name ' (' id ' int () NOT null COMMENT ' name ID ', ' name ' varchar (a) ' NOT null COMMENT ' name ', PRIMARY KEY ( ' id ') engine=innodb DEFAULT Charset=utf8;

DDL for Tbl_name_log:

DROP TABLE IF EXISTS ' Tbl_name_log ';  CREATE TABLE ' Tbl_name_log ' (' id ' int () NOT NULL auto_increment COMMENT ' audit id ', ' action ' varchar) NOT NULL COMMENT ' Operations performed in Tbl_name table ', ' name ' varchar (NOT null COMMENT ' operation name ', ' Time ' datetime DEFAULT NULL on UPDATE Current_timestam P COMMENT ' Operation Time ', PRIMARY KEY (' id ')) engine=innodb auto_increment=1 DEFAULT Charset=utf8;

3 triggers can be added to the tbl_name after three events in the Tbl_name are recorded in the Tbl_name_log table Insert/update/delete:

drop trigger if exists  ' Audit_name_add ';D elimiter ;; create trigger  ' Audit_name_add '  AFTER INSERT ON  ' tbl_name '  for each  ROW  #在tbl_name插入后在tbl_name_log记录日志的触发器BEGIN   INSERT INTO  ' tbl_name_log '   (' Action ', ' name ', ' Time ') values   (' Add ', new ' name ', now ()); END;;D elimiter ;D rop trigger if exists  ' audit_name_update ';D elimiter ;; create trigger  ' audit_name_update '  AFTER UPDATE ON  ' tbl_name '  for each  ROW  #在tbl_name更新后在tbl_name_log记录日志的触发器BEGIN   INSERT INTO  ' tbl_name_log '   (' Action ', ' name ', ' Time ') values   (' Update ', new ' name ', now ()); END;;D elimiter ;D rop trigger if exists  ' Audit_name_del ';D elimiter ;; create trigger  ' Audit_name_del '  AFTER DELETE ON  ' tbl_name '  for each  ROW  #在tbl_name删除后在tbl_name_log记录日志的触Generator begin  insert into  ' Tbl_name_log '   (' action ', ' name ', ' Time ') values   (' del ', Old. ' Name ', now ()); END;;D elimiter ;

Test, the Tbl_name table in the INSERT, UPDATE, delete will have a corresponding record in the Tbl_name_log table

MySQL Flip-flop