first, the concept of triggers
A trigger is a special stored procedure, in which the stored procedure is invoked with call, and the trigger does not need to be called, nor does it need to be started manually, as long as a predefined event occurs and is automatically called by MySQL.
A trigger is a special kind of transaction that can monitor a data operation (Insert/update/delete) and trigger related actions (Insert/update/delete).
Second, the operation of the trigger1. Create a Trigger
CREATE TRIGGERtrigger_name trigger_time trigger_event onTbl_name forEach ROW trigger_stmt----------------------------------------------------------Create TriggerTriggernameafter/BeforeInsert/Update/Delete on --Table name forEach row--this sentence is fixed.BeginSQL statements;--one or more sentences, within the range of Insert/update/deleteEnd;
- A trigger is a named database object related to a table that is activated when a specific event occurs on the table. The trigger is related to a table named Tbl_name. Tbl_name must reference a persistent table. You cannot associate a trigger with a staging table or view.
- Trigger_time is the action time of the triggering program. It can be before or after to indicate that the trigger was triggered before or after the statement that activated it.
- Trigger_event indicates the type of statement that activates the trigger. Trigger_event can be one of the following values:
Insert: Activates the trigger when inserting a new row into the table, for example, through the INSERT, LOAD data, and replace statements.
Update: Activates a trigger when a row is changed, for example, through an UPDATE statement.
Delete: Activates the trigger when a row is deleted from the table, for example, through the Delete and replace statements.
- For each row is a fixed statement, currently MySQL only supports line operations
- TRIGGER_STMT is the statement that executes when the triggering program is activated. If you plan to execute multiple statements, you can use the BEGIN ... End Compound statement structure. This allows you to use the same statements that are allowed in the stored subroutine.
To create an instance:
--before insertingCREATE TRIGGERTRI_BEFORE_INSERT_TB1 beforeINSERT onTb1 forEach ROWBEGIN ...END--after insertingCREATE TRIGGERTRI_AFTER_INSERT_TB1 afterINSERT onTb1 forEach ROWBEGIN ...END--before deletingCREATE TRIGGERTRI_BEFORE_DELETE_TB1 beforeDELETE onTb1 forEach ROWBEGIN ...END--after deletionCREATE TRIGGERTRI_AFTER_DELETE_TB1 afterDELETE onTb1 forEach ROWBEGIN ...END--before updateCREATE TRIGGERTRI_BEFORE_UPDATE_TB1 beforeUPDATE onTb1 forEach ROWBEGIN ...END--after the updateCREATE TRIGGERTRI_AFTER_UPDATE_TB1 afterUPDATE onTb1 forEach ROWBEGIN ...END
It is best to check the trigger first, and currently a table supports only one trigger of the same type:
drop trigger if exists tri_before_insert_tb1; Delimiter $$ trigger Tri_ BEFORE_INSERT_TB1 before insert on A Span style= "color: #0000ff;" >for each ROW begin insert into B (bname) value ( " Span style= "color: #ff0000;" >GGGGGGGGGGGGG " ); end $ $delimiter; show TRIGGERs
drop trigger if exists tri_before_insert_tb1; Delimiter $$ trigger Tri_ BEFORE_INSERT_TB1 after insert on A for each ROW begin insert into B (bname) value ( " Span style= "color: #ff0000;" >GGGGGGGGGGGGG " ); end $ $delimiter; show TRIGGERs
How to reference a row's value in a trigger
For insert, the new row is represented by new, and the value of each column in the row is represented by the new column name.
For delete, there was a row, then deleted, to refer to the deleted line, with old, to represent, old. Column names, you can refer to the values in the deleted row.
For update, the row that was modified.
Pre-modified data, old. Column names refer to values in rows before they are modified
Modified data, denoted by new, new. Column name references the value in the row after being modified
The difference between after and before in a trigger
After is the first to complete the data increase, delete, change the trigger, trigger the statement later than the monitoring of the increase, delete, change, can not affect the previous additions and deletions to change the action.
Before is the first to complete the trigger, then increase the deletion, trigger the statement before the monitoring of the increase, delete, change occurs, we have the opportunity to judge, modify the upcoming operation.
Second, delete the trigger
DROP TRIGGER tri_after_insert_tb1;
Iii. use of Triggers
Triggers cannot be called directly by the user, but are known to be passively triggered by the "Add/delete/change" operation on the table.