Xi. MySQL Trigger

MySQL triggers are like stored procedures, and are embedded in MySQL. A trigger is a time to trigger an action that includes insert, uodate, and DELETE statements. If a trigger is defined, when the database executes these statements it triggers the corresponding action, which is the named database object associated with the table when the object is activated when a specific event occurs on the table.

11.1. Create a Trigger

The trigger is a special stored procedure, but the execution of the stored procedure is invoked using the call statement, and the execution of the trigger does not need to be invoked using the call statement, nor does it need to be manually started, as long as the predefined time occurs, it is automatically called by MySQL.

The syntax for creating a trigger is:

CREATE TRIGGER trigger_name trigger_time trigger_event on tbl_name for each ROW trigger_body

Trigger_name: Trigger Name, user-specified Trigger_time: Trigger timing, can be specified as before or after Trigger_event: Identity Trigger Event Tbl_name: The name of the table that identifies the trigger Trigger_body: Trigger Execution statement

Create a trigger for a single execution statement:

Mysql> CREATE TABLE Account (Acc_num int,amount DECIMAL (10,2)); Query OK, 0 rows affected (0.06 sec) mysql> CREATE TRIGGER ins_sum before INSERT on account--with each ROW SET @s Um = @sum +new.amount; Query OK, 0 rows affected (0.02 sec) mysql> SET @sum = 0; Query OK, 0 rows Affected (0.00 sec) mysql> INSERT into account VALUES (1,1.00), (2,2.00); Query OK, 2 rows affected (0.02 sec) records:2 duplicates:0 warnings:0mysql> SELECT @sum; +------+| @sum |+------+| 3.00 |+------+1 row in Set (0.00 sec)

Syntax for creating triggers for multiple execution statements:

CREATE TRIGGER trigger_name trigger_time Trigger_eventon tbl_name for each ROW BEGIN trigger_stmtend

11.2. View triggers

Viewing a trigger is a view of the definition, status, and trigger information for a trigger that already exists in the database, such as the trigger information that can be viewed through show truggers and in the Triggers table.

mysql> create table myevent    ->  (    ->  id int (one)  default null,    -> evt_name char   default null    -> ); query ok, 0 rows affected  (0.03 sec) Mysql> create trigger trig _update after update on account    -> for each row  INSERT INTO myevent VALUES  (1, ' after update '); query ok, 0 rows affected  (0.05 sec) mysql> show triggers \g***  1. row ***************************              Trigger: ins_sum                Event: INSERT               Table: account            Statement: SET  @sum  =  @sum  +NEW.amount               Timing: BEFORE              Created: NULL             sql_mode:               definer: [email protected]character_set_client: utf8collation_ connection: utf8_general_ci  database collation: utf8_general_ci*********************  2. row ***************************              Trigger: trig_update                evenT: update               table:  account           Statement: INSERT  into myevent values  (1, ' after update ')                Timing: AFTER              Created: NULL             sql_mode:              definer:  [email protected]character_set_client: utf8collation_connection: utf8_general_ci   Database Collation: utf8_general_ci2 rows in set  (0.00 SEC)

The definition of all triggers in MySQL exists in the triggers table of the INFORMATION_SCHEMA database, which can be viewed through select, with the syntax in the following format:

SELECT * from INFORMATION_SCHEMA. TRIGGERS WHERE condition;

Mysql> select * from information_schema. Triggers where trigger_name= ' Trig_update '  \g*************************** 1. row *            trigger_catalog:  def            trigger_schema: test               trigger_name: trig_ update        event_manipulation: update       EVENT_OBJECT_CATALOG: def       EVENT_OBJECT_SCHEMA:  test        event_object_table: account               ACTION_ORDER: 0           action_condition: null          action_statement: insert into myevent  values  (1, ' after update ')         ACTION_ORIENTATION:  row             action_timing:  Afteraction_reference_old_table: nullaction_reference_new_table: null  action_reference_ old_row: old  action_reference_new_row: new                    CREATED: NULL                   SQL_MODE:                      definer: [email protected]      character_set_client: utf8       collation_connection: utf8_general_ci        database_collation:  utf8_general_ci1 row in set  (0.00 SEC)

11.3, the use of triggers

A trigger is a named database object related to a table that is activated when a specific time appears on the table.

Creates a trigger that updates the MyEvent data table after the Account table has inserted data.

mysql> create trigger trig_inster after  insert on account     -> for each row insert  into myevent values  (2, ' After insert ');  query ok, 0 rows affected   (0.02 SEC) mysql> insert into account values  (1,1.00), (2,2.00); query ok, 2 rows affected  (0.01 sec) records: 2  duplicates:  0  warnings: 0mysql> select * from myevent;+------+--------------+|  id   | evt_name     |+------+--------------+|     2 | after insert | |     2 | after insert |+------+--------------+2 rows in  set  (0.00 sec) 

11.4. Delete Trigger

Use the DROP TRIGGER statement to delete a trigger that already exists in MySQL in the syntax format:

DROP TRIGGER [schema_name.] Trigger_name

Delete a trigger

mysql> DROP TRIGGER test.ins_sum; Query OK, 0 rows affected (0.02 sec)

This article is from the "Wind and Drift" blog, please be sure to keep this source http://yinsuifeng.blog.51cto.com/10173491/1953884

Xi. MySQL Trigger