Trigger before in database and after cognition

Before and after differences:

before: (INSERT, UPDATE) can modify new, after cannot modify new, Neither can modify the old data.

For INSERT statements, only new is valid;

For DELETE statements, only old is valid;

For UPDATE statements, NEW and old can be used at the same time.

Examples are as follows:

1Mysql> Select *  fromT1;2 +----+-------+3 |Id|Name|4 +----+-------+5 | 1 |Zhang|6 +----+-------+

1Mysql>Delimiter//2Mysql> Create TriggerTbbefore Update  onT13      -  forEach row4      - begin5      - SetNew.id=New.id+5;6      - SetNew.name=Upper(new.name);7      - Insert  intoT2Values(new.id,new.name);8      - End;//

1 delimiter return semicolon 2 mysql> delimiter;

1Mysql> UpdateT1SetName='Huang' whereId=1;2Query OK,1Row affected (0.01sec)3Rows matched:1Changed:1Warnings:04 5Mysql> Select *  fromT1; 6 +----+-------+7 |Id|Name|8 +----+-------+9 |  6 |HUANG|Ten +----+-------+ One 1Rowinch Set(0.00Sec

1Mysql> Select *  fromT2;2 +----+-------+3 |Id|Name|4 +----+-------+5 |  6 |HUANG|6 +----+-------+7 1Rowinch Set(0.00Sec

From the above, if trigger is using before, you can modify the value of new and write to the database

1 The same code, change the before to after, will be an error2Mysql>Delimiter//3Mysql> Create TriggerTa After Update  onT14      -  forEach row5      - begin6      - SetNew.id=New.id+5;7      - SetNew.name=Upper(new.name);8      - Insert  intoT2Values(new.id,new.name);9      - End;//TenERROR1362(HY000): Updating ofNEW row is  notAllowedinchAfterTrigger

Overwrite after content as follows: normal operation

1Mysql>Delimiter//2Mysql> Create TriggerTa afterUpdate  onT1 forEach row3      - begin4      - Insert  intoT2Values(old.id,new.name);5      - End;//6Query OK,0Rows Affected (0.00sec)7 8Mysql>delimiter;9Mysql> UpdateT1SetName='Chen' whereId=6;Ten  OneMysql> Select *  fromT1; A +----+------+ - |Id|Name| - +----+------+ the |  6 |Chen| - +----+------+ - 1Rowinch Set(0.00sec) -  +Mysql> Select *  fromT2; - +----+-------+ + |Id|Name| A +----+-------+ at |  6 |HUANG| - |  6 |Chen| - +----+-------+ - 2Rowsinch Set(0.00Sec

Summarized as follows:

After-is triggered after recording manipulation, is the first to complete the data additions and deletions, and then trigger, trigger the statement later than the monitoring of the deletion and modification operation, can not affect the previous additions and deletions to change the action
Before-is triggered before recording manipulation, is the first to complete the trigger, and then increase the deletion, trigger the statement before the deletion and modification of the monitoring, we have the opportunity to judge, modify the impending operation, such as: we need to determine the new value and the old value of the size or relationship before triggering, if the requirements are triggered, Do not pass the modification and trigger, such as: table between the definition of a foreign key, in the deletion of the primary key, you must first delete the foreign key table, then there are successively points, here before equivalent to set the breakpoint, we can handle the deletion of foreign keys.

Trigger before in database and after cognition