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. Only when a pre-defined event occurs will it be invoked by MySQL on its own initiative.

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 transient table or view.

Trigger_time is the action time of the triggering program. It can be either 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 of the examples that can cause 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, the details depend on whether there are repeated keys on the line.

There cannot be two triggers for a given table with the same trigger action time and event.

For example, there cannot be 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 runs when the triggering program is activated.

Suppose you intend to run multiple statements. You can use the BEGIN ... End Compound statement structure. This allows you to use the same statement that you agreed to in the stored subroutine.

Create a trigger for a single-run 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.

To sum the newly inserted amount field values

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

Start by creating an account table before inserting data into the table account. Calculates the sum of the amount values for all newly inserted account tables.

The name of the trigger is ins_num. The condition is triggered before data is inserted into the table.

Create a trigger with more than one running statement, such as 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.

Ability to 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 events that activate the trigger, where the trigger event is an insert operation. Table represents the object tables that activate the trigger, which is the account table

Timing represents the time of the trigger, before insertion (before). Statement represents the action that the trigger runs, and other information. For example, SQL mode. Definition of triggers for accounts and character sets, 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 for detailed actions that are run when a trigger is triggered

Action_orientation is row. 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 remove a trigger that has already been defined in MySQL and delete the basic syntax of the trigger

DROP TRIGGER [schema_name.] Trigger_name

(schema_name) is optional

Suppose the schema (scheme) 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, the same event can only create a trigger, such as creating a before insert trigger on the table account

So if you create a before insert trigger on the table account again, MySQL will make an error, and you can only

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 '

MySQL optimization--Trigger