MySQL optimization--Trigger

Reprint Please specify source: http://blog.csdn.net/l1028386804/article/details/46763665

A trigger is a special stored procedure, in which the stored procedure is called with call, and the trigger does not need to use the calling

It does not need to be started manually, as long as a predefined event occurs, it is automatically called by MySQL.

Create a Trigger

The syntax is as follows:

CREATE TRIGGER trigger_name trigger_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 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.

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 before update 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.

Create a trigger for a single execution statement

CREATE TABLE Account (acct_num INT, amount DECIMAL (10,2)); CREATE TRIGGER ins_sum before INSERT on accountfor each ROW SET @[email protected]+new.amount;

First, create an Account table with two fields in the table: Acct_num field (defined as int type)

Amount field (defined as floating-point type), followed by a trigger named Ins_sum, which triggers the condition that the data table account is inserted before the data is

To sum the newly inserted amount field values

DECLARE @num intset @num =0insert into account VALUES (1,1.00), (2,2.00) SELECT @num

First, create an account table that calculates the sum of the amount values for all newly-inserted calculation tables before inserting the data into the table.

The name of the trigger is Ins_num, which is triggered before the data is inserted into the table.

Create a trigger with more than one execution statement, with the following syntax:

DELIMITER | CREATE TRIGGER testref before insert on test1 for each  ROW BEGIN    INSERT into test2 SET a2 = new.a1;    DELETE from test3 WHERE a3 = new.a1;      UPDATE test4 SET b4 = b4 + 1 WHERE a4 = new.a1;  end|

View triggers

Viewing a trigger is the definition, status, syntax information, and so on, of a trigger that already exists in the database.

You can use show TRIGGERS and view trigger information in the TRIGGERS table

SHOW TRIGGERS Trigger  Event   Table    Statement                 Timing  Created  sql_mode  definer         Character_set_client  collation_connection  Database collation-------  ------  -------  ----- -------------------  ------  -------  --------  --------------  --------------------  - -------------------  ------------------ins_sum  INSERT  account  set @[email protected]+ New.amount  before  (NULL)             [email protected]  UTF8                  utf8_general_ci       utf8_general_ci   the event represents the activity that activates the trigger, where the trigger event is the insert Operation insert,table the Object table that represents the activation trigger, which is the account table

Timing represents the time of the trigger, before insertion (before), Statement represents the action performed by the trigger, and some other information, such as SQL schema, trigger definition account and character set, etc.

Viewing trigger information in the Triggers table

INFORMATION_SCHEMA database in the Triggers table, you can view the trigger information through the query

SELECT * from ' information_schema '. ' TRIGGERS ' WHERE ' trigger_name ' = ' ins_sum ' trigger_catalog trigger_schema trigger_ NAME event_manipulation event_object_catalog event_object_schema event_object_table ACTION_ORDER ACTION_CONDITION A Ction_statement action_orientation action_timing action_reference_old_table action_reference_new_table ACTION _reference_old_row action_reference_new_row CREATED sql_mode definer character_set_client COLLATION_CONNECTIO N database_collation-------------------------------------------------------------------------------------------  -------  ------------------  ------------  ----------------  ------------------------  ------------------  ------------- --------------------------  --------------------------  ------------------------  ------------------------  -------  ---- ----------------------------------------------------------------------------def school ins_s Um INSERT DEF School Account 0 (NULL) set @[email protected]+                       New.amount ROW before (null) (NULL) old NEW (NULL) [email protected] UTF8 utf8_general_ci ut    F8_general_ci

Trigger_schema represents the database where the trigger resides

Trigger_name indicates the name of the trigger

Event_object_table indicates on which table the trigger

Action_statement represents the specific action that is performed when the trigger is triggered

Action_orientation is row, which indicates that it is triggered on each record

Action_timing indicates that the moment of triggering is before

Delete Trigger

Use the Drop TRIGGER statement to delete a trigger that has already been defined in MySQL and delete the basic syntax of the trigger

DROP TRIGGER [schema_name.] Trigger_name

where (schema_name) is optional

If schema is omitted, the trigger is discarded from the current scenario.

Delete Ins_sum Trigger

DROP TRIGGER ' school '. ' Ins_sum '

Trigger Ins_sum Delete Succeeded

Summarize

For the same table, you can create only one trigger for the same event, such as creating a before insert trigger on the table account

Then if you create a before insert trigger on the table account again, MySQL will error, at this point, only on the table account

Create a trigger for after insert or before update type

Add:

CREATE TABLE account (acct_num int, amount DECIMAL (10,2), num int); CREATE TRIGGER ins_sum before INSERT on account for each ROW UPDATE   ' employee ' SET  ' age ' = new.acct_num + 1 WHERE ' ID ' = new.acct_num; INSERT into account (Acct_num,amount) VALUES (1,2.00); SELECT * from ' employee '

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

MySQL optimization--Trigger