Shulou(Shulou.com)06/01 Report--

Q: what is a trigger?

A:

A trigger is a database object associated with a table that fires when a definition condition is met and executes a collection of statements defined in the trigger.

Characteristics of triggers:

1. With begin end body, statements between begin end; can be written simply or complicately.

2. What conditions will trigger: I, D, U

3. When to trigger: before or after the addition, deletion or modification

4. Trigger frequency: execute for each line

5. The trigger is defined and attached to the table.

That is, an action is triggered by events, including INSERT statements, UPDATE statements, and DELETE statements; it can help applications ensure data integrity on the database side.

Note: cannot associate a trigger with a TEMPORARY table or a view.

!! Use triggers as little as possible and are not recommended.

Assuming that the trigger triggers 1s insert table 500 pieces of data per execution, then the trigger needs to be triggered 500 times, and the trigger execution time alone takes 500s, while the total insert 500 pieces of data is 1 s, then the efficiency of this insert is very low. Therefore, we need to pay special attention to the fact that the execution efficiency of the statements between the begin end; of the trigger must be high and the resource consumption must be low.

Use triggers as little as possible, because in any case, it is still very resource-consuming, and if used carefully, make sure it is very efficient: triggers are for each row; don't use triggers on tables that add, delete and change very frequently, because it consumes a lot of resources.

First, create a trigger

CREATE [DEFINER = {user | CURRENT_USER}] TRIGGER trigger_nametrigger_time trigger_eventON tbl_name FOR EACH ROW [trigger_order] trigger_bodytrigger_time: {BEFORE | AFTER} trigger_event: {INSERT | UPDATE | DELETE} trigger_order: {FOLLOWS | PRECEDES} other_trigger_name

> the BEFORE and AFTER parameters specify the time to trigger execution, before or after the event.

> FOR EACH ROW means that any action on a record that satisfies the trigger event will trigger the trigger, that is, the trigger frequency is triggered once for each row of data.

> tigger_event details:

① insert trigger: activates a trigger when a row is inserted, which may be triggered by INSERT, LOAD DATA, or REPLACE statements (LOAD DAT statements are used to load a file into a data table, which is equivalent to a series of INSERT operations)

② update trigger: activates a trigger when a row is changed, which may be triggered by a UPDATE statement

③ delete trigger: activates the trigger when a row is deleted, which may be triggered by DELETE or REPLACE statements.

> trigger_order is a function after MySQL5.7 that defines multiple triggers, using follows (trailing) or precedes (in … To select the order in which triggers are executed.

1. Create a trigger with only one execution statement

CREATE TRIGGER trigger name BEFORE | AFTER trigger event ON table name FOR EACH ROW execute statement

Example 1: a trigger named trig1 is created. Once there is an insert action in the work table, the current time is automatically inserted into the time table.

Mysql > CREATE TRIGGER trig1 AFTER INSERT-> ON work FOR EACH ROW-> INSERT INTO time VALUES (NOW ())

2. Create a trigger with multiple execution statements

CREATE TRIGGER trigger name BEFORE | AFTER trigger event

ON table name FOR EACH ROW

BEGIN

Execute statement list

END

Example 2: define a trigger that executes statements in BEGIN and END once there is a delete operation that satisfies the condition

Mysql > DELIMITER | | mysql > CREATE TRIGGER trig2 BEFORE DELETE-> ON work FOR EACH ROW-> BEGIN-> INSERT INTO time VALUES (NOW ());-> INSERT INTO time VALUES (NOW ());-> END | | mysql > DELIMITER

3. Detailed explanation of NEW and OLD

NEW and OLD are defined in MySQL to represent the row of data in which the trigger is triggered in the table where the trigger is located, to refer to the changed records in the trigger, specifically:

① in an insert trigger, NEW is used to represent new data that will be (BEFORE) or has been (AFTER) inserted.

② in the update trigger, OLD is used to represent the original data that will or has been modified, and NEW is used to represent the new data to be or has been modified.

③ in a delete trigger, OLD is used to represent the original data that will be or has been deleted

How to use it:

NEW.columnName (columnName is a column name of the corresponding data table)

In addition, OLD is read-only, while NEW can use SET assignment in the trigger so that it does not trigger the trigger again, resulting in a circular call (such as adding "2013" before each student is inserted).

Example 3:

Mysql > CREATE TABLE account (acct_num INT, amount DECIMAL (10Power2)); mysql > INSERT INTO account VALUES (137Mae 14.98), (141Ma197.50), (97Leirelle 100.00); mysql > delimiter $$mysql > CREATE TRIGGER upd_check BEFORE UPDATE ON account-> FOR EACH ROW-> BEGIN-> IF NEW.amount

SET NEW.amount = 0;-> ELSEIF NEW.amount > 100THEN-> SET NEW.amount = 100;-> END IF;-> END$$mysql > delimiter; mysql > update account set amount=-10 where acct_num=137;mysql > select * from account +-+-+ | acct_num | amount | +-+-+ | 137 | 0.00 | 1937.50 | 97 |-100.00 | +-+-+ mysql > update account set amount=200 where acct_num=137;mysql > select * from account +-+-+ | acct_num | amount | +-+-+ | 100.00 | 100.00 | 1937.50 | 97 |-100.00 | +-+-+

Second, view triggers

1. SHOW TRIGGERS statement to view trigger information

Mysql > SHOW TRIGGERS\ G

……

As a result, the basic information for all triggers is displayed; the specified trigger cannot be queried.

2. View the trigger information in the information_ schema.triggers table

Mysql > SELECT * FROM information_schema.triggers\ G

……

As a result, the details of all triggers are displayed; at the same time, the method can query the details of the trigger.

Mysql > select * from information_schema.triggers-> where trigger_name='upd_check'\ G

Tips:

All trigger information is stored in the triggers table under the information_schema database and can be queried using a select statement. If there is too much trigger information, it is best to specify the query through the TRIGGER_NAME field.

Third, delete the trigger

DROP TRIGGER [IF EXISTS] [schema_name.] trigger_name

It is best to use the above method after deleting triggers; at the same time, you can also use database.trig to specify triggers in a database.

Tips:

It is critical to delete a trigger if it is not needed so as not to cause unexpected operations.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.