MySQL Flip-flop usage detailed

MySQL Trigger syntax:

Trigger trigger is a special stored procedure that triggers execution when inserting (inset), deleting (delete), or modifying (update) data in a particular table, which is more granular and more complex than the data itself. A trigger is not called by a program, but is triggered by an event. Automatically enforce their business rules when data is modified, often to enforce data integrity constraints and business rules. Triggers can query other tables and contain copied SQL statements. Triggers can also be used to enforce referential integrity. Triggers can enforce more complex constraints than constraints defined with a CHECK constraint.

(i). CREATE Trigger Syntax
CREATE TRIGGER trigger_nametrigger_time trigger_event on tbl_name for each ROW trigger_stmt;
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 temporary 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:
(1). Insert: Activates the trigger when inserting a new row into the table, for example, through INSERT, LOAD data, and replace
Statement.
(2). Update: Activates a trigger when a row is changed, for example, through an UPDATE statement.

(3). Delete: Activates the trigger when a row is deleted from the table, for example, through the Delete and replace statements.
It is important to note that trigger_event is not very similar to the SQL statement that activates the trigger as a table operation. For example, the before trigger on insert can be activated not only by the INSERT statement, but also by the load data statement. One example of possible confusion is insert into. On DUPLICATE UPDATE ... Syntax: The before INSERT trigger is activated for each row, followed by the after insert trigger, or before update and after update triggers, depending on whether there are duplicate keys on the row.
There cannot be two triggers for a given table with the same trigger action time and event. For example, you cannot have two before update triggers for a table. However, there can be 1 before update triggers and one before insert trigger, or 1 beforeupdate triggers and one after UPDATE trigger. 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

(b). DROP Trigger Syntax
DROP Trigger[schema_name.] Trigger_name discards the triggering program. The scheme name (schema_name) is optional. If schema is omitted, the trigger is discarded from the current scenario.
Note: When upgrading from MySQL version prior to MySQL 5.0.10 to 5.0.10 or later (including all MySQL5.1 versions), you must discard all triggers before upgrading and recreate them later, otherwise the drop trigger will not work after the upgrade. The DROP trigger statement requires Super permissions.

(iii). Using triggers
      In this section, the methods for using triggers in MySQL 5.1 are described, and the limitations on using triggers are described. The
      trigger is a named database object related to a table that is activated when a specific event occurs on the table. In the usage of some triggers, you can use it to check the values that are inserted into the table, or to evaluate the values involved in the update.  
      triggers are related to tables, and triggers are activated when an INSERT, delete, or UPDATE statement is executed against a table. You can set the trigger to be activated before or after the execution of the statement. For example, you can activate the trigger before each row is deleted from the table, or after each row has been updated. To create a trigger or discard a trigger, use the CREATE TRIGGER or DROP TRIGGER statement. The trigger cannot invoke the stored program that returns the data to the client, nor can it use dynamic SQL with the Call statement (which allows the stored program to return data to the trigger via parameters).
      Triggers cannot use statements that start or end transactions explicitly or implicitly, such as Start TRANSACTION,
Commit, or rollback.
      Use the old and new keywords to access the columns in the rows affected by the trigger (old and new are not case sensitive).
      in the Insert trigger, only new.col_name can be used and there are no old rows. In the delete trigger, only old.col_name can be used and no new rows are available. In the update trigger, you can use Old.col_name to refer to the column of a row before the update, or you can use New.col_name to refer to the columns in the updated row. The
column named with old is read-only. You can reference it, but you can't change it. For a column named with new, you can refer to it if you have SELECT permission. In the Before trigger, if you have update permissions, you can change its value using SET New.col_name = value. This means that you can use the trigger to change the value that will be inserted into the new row, or to update the value of the row. In the Before trigger, the new value of the Auto_increment column is 0, not the sequence number that will be automatically generated when a new record is actually inserted.

      by using the BEGIN ... End structure to define triggers that execute multiple statements. In the Begin block, you can also use other syntax that is allowed in the stored subroutine, such as conditions and loops. However, as with stored subroutines, when you define a trigger that executes multiple statements, if you use a MySQL program to enter a trigger, you need to redefine the statement delimiter so that you can use the character ";" in the trigger definition. In the following example, these points are demonstrated. In this example, 1 update triggers are defined to check the new values that will be used when each row is updated, and to change the value so that it is in the range of 0~100. It must be a before trigger, because it needs to be checked before the value is used to update the row:
mysql> delimiter//
mysql> CREATE TRIGGER Upd_check before UPDATE On account
    for each ROW
   , BEGIN
   , IF NEW . amount < 0 then
   -> set new.amount = 0;
    ELSEIF new.amount > then
   ->  set new.amount = +;
   ->  end IF;
    end;//
mysql> delimiter;
     A simpler approach is to define the stored program separately and then invoke the stored program from the trigger using a simple call statement. This method is also helpful if you are going to invoke the same subroutine from within several trigger programs. During the execution of the trigger, MySQL handles the error as follows:
   (1) If the before trigger fails, the action on the corresponding line is not performed.

(2) The After trigger is executed only if the before trigger (if any) and the row operation have been executed successfully.

(3) If an error occurs during the execution of the before or after trigger, the failure of the entire statement that invokes the trigger is caused.
(4) For a transactional table, any changes that the statement makes will be rolled back if the triggering program fails (and the resulting failure of the entire statement). For non-transactional tables, this type of rollback cannot be performed, so any changes made before the failure are valid even if the statement fails.

Example one:
Mysql> CREATE TABLE Account (acct_num INT, amount DECIMAL (10,2));
Mysql> CREATE TRIGGER ins_sum before INSERT on account for each ROW SET @sum = @sum + new.amount;

MySQL Flip-flop usage detailed