mysql--Trigger

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

Small Instances

It is best to check the trigger first, and currently a table supports only one trigger of the same type:

Show triggers;

 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  

insert pre-trigger

 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  

post-Insert trigger

Attention:

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

Attention:

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.

mysql--Trigger