1000 Lines MySQL study notes (eight)

/ * Trigger */------------------

a trigger is a named database object that is related to a table, and when a specific event occurs, the object is activated for monitoring: Record additions, modifications, and deletions. 

--Create a trigger
 
 CREATE TRIGGER trigger_name trigger_time trigger_event  on Tbl_name  for each ROW trigger_ stmt
Parameters:
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 activated the trigger
Insert  : Activates the trigger when inserting a new row into a table  
 UPDATE: Activating a trigger when a row is changed  
 Delete: Activates the trigger when a row is deleted from the table
 tbl_name: A table that listens, must be a persistent table, and cannot associate a trigger with a temporary table or view.
TRIGGER_STMT: The statement executed when the trigger is activated. To execute multiple statements, you can use the BEGIN ... End Compound statement structure

--Delete
 
 DROP TRIGGER [schema_name.]    The trigger_name can use old and new to replace older and newer data updates, which are pre-existing and updated with new.    Delete operation, only old. Add operation, only new.

--Note
1. There can be no two triggers for a given table with the same trigger action time and event. 

--Character join function
 
concat (str1[, str2,...])

--Branch statements
 
 if condition  Then execute statement elseif condition  then execute statement
 Else EXECUTE statement
 End If;

--Modify the outermost statement Terminator
delimiter custom end symbol SQL statement custom end symbol delimiter; --Change back to the original semicolon 
 
--statement block wrapping
 
 begin Statement block
End

--Special execution
 
1. The program is triggered whenever a record is added. 
2. insert into on duplicate key update syntax triggers: if No duplicates are recorded, before insertis triggered, after    Insert;    If there is duplicate record and update, it will trigger before insert, before update, after update; Trigger before insert, before update If there are duplicate records but no updates have occurred
3. Replace Syntax If there is a record, execute before insert, before delete, after delete, After insert 

(Shocker Source: http://www.cnblogs.com/shockerli/p/1000-plus-line-mysql-notes.html)

1000 Lines MySQL study notes (eight)