MySQL Optimization-triggers and mysql triggers

MySQL Optimization-triggers and mysql triggers

Reprinted please indicate the source: http://blog.csdn.net/l1028386804/article/details/46763665

A trigger is a special stored procedure. The difference is that the stored procedure must be called by CALL, and the trigger does not need to use CALL.

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

Create a trigger

Syntax:

CREATE TRIGGER trigger_name trigger_time trigger_event    ON tbl_name FOR EACH ROW trigger_stmt

A trigger is a table-related named database object. This object is activated when a specific event occurs on the table.

The trigger program is related to the table named tbl_name. Tbl_name must reference a permanent table. The trigger program cannot be associated with a temporary table or view.

Trigger_time is the time when the program is triggered. It can be BEFORE or AFTER to indicate that the trigger program is triggered BEFORE or AFTER its statement is activated.

Trigger_event indicates the type of statements used to activate the trigger program. Trigger_event can be one of the following values:

· INSERT: the trigger program is activated when a new row is inserted into the table, for example, through INSERT, load data, and REPLACE statements.

· UPDATE: the trigger program is activated when a row is changed, for example, through the UPDATE statement.

· DELETE: the trigger program is activated when a row is deleted from the table, for example, through the DELETE and REPLACE statements.

Note that trigger_event is not very similar to the SQL statement used to activate the trigger program in the form of table operations, which is very important.

For example, the BEFORE trigger program for INSERT can not only be activated by the INSERT statement, but also beLOAD DATAStatement activation.

One of the examples that may cause confusion is insert .. on duplicate update... syntax: The before insert trigger will be activated for each row, followed by the after insert trigger program, or the before update and after update trigger programs, depending on whether there is a duplicate key on the row.

For a given table with the same triggering program action time and event, there cannot be two triggering programs.

For example, a table cannot have twoBEFORE UPDATETrigger the program.

However, there can be one before update trigger program, one before insert trigger program, one before update trigger program, and one after update trigger program.

Trigger_stmt is the statement executed when the trigger program is activated.

If you want to execute multiple statements, you can use the in... END compound statement structure. In this way, the same statements allowed in the stored subroutine can be used.

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 @SUM=@SUM+new.amount;

First, create an account table with two fields: acct_num (defined as int type)

The amount field (defined as a floating point type). Then, create a trigger named ins_sum, which triggers the condition that before inserting data to the account of the data table,

Calculates the sum of 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. before inserting data into the table account, calculate the sum of the amount values of all newly inserted account tables,

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

Create a trigger with multiple execution statements. The syntax is as follows:

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

View a trigger is the definition, status, and syntax information of an existing trigger in the database.

You can use show triggers and TRIGGERS to view TRIGGERS information in the TRIGGERS table.

Show triggers Trigger Event Table Statement Timing Created SQL _mode Definer character_set_client collation_connection Database Collation ------- ------ ------- begin ------ ------- -------- ------------ begin transaction ins_sum INSERT account set @ sum = @ sum + new. amount BEFORE (NULL) root @ localhost utf8 utf8_general_ci utf8_general_ci EVENT indicates the EVENT for activating the trigger. The trigger EVENT here is INSERT, and TABLE indicates the object TABLE for activating the trigger. Here is the account TABLE.

Timing indicates the trigger time, BEFORE insertion (BEFORE); Statement indicates the action performed by the trigger, and other information, such as the SQL mode, trigger definition account, and character set.

View trigger information in the TRIGGERS table

In the information_schema database TRIGGERS table, you can query and view trigger information.

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  ACTION_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_CONNECTION  DATABASE_COLLATION---------------  --------------  ------------  ------------------  --------------------  -------------------  ------------------  ------------  ----------------  ------------------------  ------------------  -------------  --------------------------  --------------------------  ------------------------  ------------------------  -------  --------  --------------  --------------------  --------------------  ------------------def              school          ins_sum       INSERT              def                   school               account                        0  (NULL)            set @sum=@sum+new.amount  ROW                 BEFORE         (NULL)                      (NULL)                      OLD                       NEW                       (NULL)             root@localhost  utf8                  utf8_general_ci       utf8_general_ci   

TRIGGER_SCHEMA indicates the database where the trigger is located.

TRIGGER_NAME indicates the trigger name.

EVENT_OBJECT_TABLE indicates on which table to trigger

ACTION_STATEMENT indicates the specific operation performed when the trigger is triggered.

ACTION_ORIENTATION is ROW, which indicates that each record is triggered.

ACTION_TIMING indicates that the trigger time is BEFORE.

Delete trigger

You can use the drop trigger statement to delete the triggers defined in MYSQL and the basic syntax of the triggers.

DROP TRIGGER [schema_name.]trigger_name

(Schema_name) is optional.

If schema is omitted, the trigger program is discarded from the current scheme.

Delete An ins_sum trigger

DROP TRIGGER `school`.`ins_sum`

Trigger ins_sum deleted successfully

Summary

For the same table, only one trigger can be created for the same event. For example, the before insert trigger is created for the table account.

If you create another before insert trigger for the table account, MYSQL will report an error.

Create an after insert or before update trigger

Supplement:

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 Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.