mysql--Trigger

The meaning and function of trigger

A trigger (trigger) is an event that triggers an action, mainly by an event such as an INSERT update Delete to trigger a particular condition;

When the trigger condition is met, the database executes the program statement defined by the trigger.

For example: When a student's record is added to the table, the total number of students must be changed at the same time. You can create a trigger here to add a student's record at a time. Perform an operation that calculates the total number of students at once. This ensures that the record statistics are kept up-to-date after each additional student.

Definition statements for triggers

# trigger for a single execution statement

Create Trigger after|  before triggering events  on  for Each  row executes the statement

# Multiple triggers to execute statements

delimiter && //Change the delimiter to && (of course, you can customize it for others) (note there are spaces)Create TriggerTrigger Nameafter|The  before trigger event //after means that the statement executes after the event is triggered; before indicates that the statement was executed before  onTable name forEach row //on indicates on which table beginEXECUTE statement 1; EXECUTE statement 2; ...;End && //with && to end this statement delimiter; //Change the delimiter back (note that there are spaces)  

Understand triggers in a simple example

Table A <---trigger T1---> B
When a table inserts a record (triggering event),
The trigger will count the total number of records in the A table (EXECUTE statement 1)and write to Table B (EXECUTE statement 2).

When the user wants to get the results of a table (the total number of records) each time, only need to go to the B table to check, thus avoiding the #select count (*) from A; the query burden of this statement

#创建A表Create TableA (useridint(Ten), usernamevarchar( -), oldint(4), Addressvarchar( -) ); #创建B表Create TableB (insert_time time,The//insert_time property is used to record the time of the insertCount int); // The Count property is used to record the total number of records in a table #创建触发器t1的过程如下delimiter &&Create TriggerT1 afterInsert      onA forEach rowbegin    DeclareAcountint; SetAcount=(Select Count(*) fromA); Insert  intoBValues(now (), acount); End &&delimiter;

#验证:
INSERT into A values (' 008 ', ' lyj88 ', ' n ', ' gz ');
......

SELECT * from B;
+-------------+-------+
| Insert_time | Count |
+-------------+-------+
|     00:41:39 | 5 |
+-------------+-------+

Statements that view trigger information

#查询当前数据库中的所有触发器信息show triggers \g; #查询全部触发器的详细信息 (all trigger information is present in the triggers table in the INFORMATION_SCHEMA library)use information_schema; SELECT * from  information_schema.triggers \g; #查询单个触发器的详细信息 SELECT *fromtriggers where trigger_name=' Product_af_delete '  \g;

To delete a trigger statement

Drop trigger trigger name;

Exercises

#创建product表Create TableProduct (IDint(Ten)Primary Key  not NULL Unique, namevarchar( -) not NULL,function varchar( -), Companyvarchar( -)Unique, Addressvarchar( -)); #创建operate表Create TableOperate (op_idint(Ten) not NULL, Op_typevarchar( -) not NULL, Op_time time not NULL); #创建 Product_bf_insert trigger delimiter&&Create TriggerProduct_bf_insert beforeInsert  onProduct forEach rowbeginDeclareCount1int(Ten);SetCount1=(Select Count(*) fromproduct);Insert  intoOperateValues(Count1,'Insert', now ());End &&delimiter; #创建product_af_update触发器delimiter&&Create TriggerProduct_af_update afterUpdate onProduct forEach rowbeginDeclareCount2int(Ten);SetCount2=(Select Count(*) fromproduct);Insert  intoOperateValues(Count2,'Update', now ());End &&delimiter; #创建product_af_delete触发器delimiter&&Create TriggerProduct_af_delete afterDelete  onProduct forEach rowbeginDeclareCount3int(Ten);SetCount3=(Select Count(*) fromproduct);Insert  intoOperateValues(Count3,'Delete', now ());End &&delimiter; #执行inset,  update, DELETE statement, you can query the related records in the operate table Insert  intoProductValues( -,'P2','Ko','Google','CH');Insert  intoProductValues(101,'P2','Ko','Baidu','CH');UpdateProductSet function='OK' whereId= -;Delete  fromProductwhereId=101;Select *  fromoperate;+-------+---------+----------+|op_id|Op_type|Op_time|+-------+---------+----------+|     0 | Insert  |  on: Wu:xx ||     1 | Insert  | Geneva: the: + ||     2 | Update  | Geneva: +: - ||     1 | Delete  | Geneva: -: - |+-------+---------+----------+

mysql--Trigger