MySQL Trigger drink update operation

First, the concept of trigger

Trigger (Trigger): Monitors a situation and triggers an action, which is a way to provide programmers and data analysts with the assurance of data integrity, which is a special stored procedure associated with a table event that is not executed by the program or manually, but triggered by events. For example, when an operation is performed on a table (Insert,delete, update), it is activated.

Triggers are often used to enforce data integrity constraints, business rules, and so on. Trigger creation syntax four elements:
1. Monitoring Location (table)
2. Monitoring Events (Insert/update/delete)
3. Trigger Time (After/before)
4. Trigger Event (Insert/update/delete)
The basic syntax for triggers is as follows:

Where: Trigger_time is a trigger event for a trigger that can be before (triggered before a constraint is checked) or after (triggered after a constraint is checked); Trigger_event is a trigger event for triggers, including INSERT, update, and delete, Note that you can define only one trigger for the same trigger event that has the same trigger time for the same table, and you use old and new to refer to the record content that has changed in the trigger.
Trigger SQL Syntax:

Create Trigger Triggername After / Insert / Update / Delete  on  for begin end  

Recommended reading: http://www.cnblogs.com/zzwlovegfj/archive/2012/07/04/2576989.html (MySQL trigger (trigger)

Use Navicat for MySQL to create a trigger method as shown, but it is recommended to use a statement operation.
First, in Navicat for MySQL, find the table that needs to establish the trigger, right-click Design table, and then create the trigger.

Second, a simple insert trigger

Suppose there is a student table (student), including the student's basic information, the school number (STUID) is the primary key.

In addition there is a score table (CJ), which corresponds to each student including a value. Where number indicates the ordinal is the primary key, and the sequence is automatically incremented. It is self-increasing by default during the insertion process. Also assume that the student's name and school number are included in the score table.

The score table currently has no value, the first need to design a trigger, when adding new students, need to insert the corresponding student information in the score table, as for the specific math, Chinese, English after the teacher score update.
So how do you design a trigger?
1. First it is an insert trigger, which is built on the table student;
2. Then after, the post-insertion event;
3. The event content is inserted in the score table, the main need to insert the student's school number and name, numbers are self-increment, and the results are not currently required.
Note: New represents the newly inserted value in the student.

Create Trigger  Insert on to begin INSERT into     CJ (stu_id, Stu_name)  valuesEnd

Create the trigger as shown in the following:

Then insert the data:

Insert Values ('eastmount','111111','6', ' 1991-12-05 ');

Two data is inserted at the same time, the trigger executes correctly ~

Note: As with the creation of triggers and tables, it is recommended to add judgment: DROP TRIGGER IF EXISTS ' Ins_stu ';

Third, call the trigger after judging the value

Here are a few simple triggers to determine the type of insertion.
For example, a trigger call, when the insertion time is 20 o'clock hours, insert the data:

DROP TRIGGER IF EXISTS' Ins_info ';Create TriggerIns_info AfterInsert  onNhfxelect forEach rowbegin  ifHOUR (new. Recordtime)=' -'  Then  Insert  intoNhfxbyhour (Unitdepname, Unitdepcode, Electcost, TIMEJG, Recordtime)Values('School of Digital Sciences','1', New. USERKWH,' -', New.  Recordtime); End if; End;

In this trigger, Recordtime is a datetime type, such as "2016-08-28 20:10:00", at which point the value of hour () is 20 to insert; otherwise the data cannot be inserted. You can also date_format (new. Recordtime, '%y-%m-%d ') determines whether the date is inserted one day or a month.
At the same time, if the trigger is updated, if the value is set to a range, the operation is performed or the gender is "male" or "female".

Basic Syntax: if  Then SQL statements; End if;

IV. UPDATE triggers-real-time updates

Suppose there is a server that inserts data in real time, such as the amount of money or electricity consumed by a student.
Stucost: Student's electricity data, real-time insertion, cost for every 30 seconds consumption amount, recordtime for every minute insertion time, datetime type;
Stucostbyhour: Statistics of the amount of money spent on one hour, hourcost for the total amount, hourly statistics, TIMEJD time period, 1~24, corresponding to hourly, recordtime for the statistical time.
Now you need to design a real-time update trigger, when inserting consumer data, hourly statistics of the student's spending, the same amount of electricity and so on.

DROP TRIGGER IF EXISTS' Upd_info ';Create TriggerUpd_info AfterInsert  onStucost forEach rowbegin  UpdateStucostbyhourSetHourcost=Hourcost+New. Costwhere(Timejd=Hour (new. Recordtime)+ 1) andDate_format (new. Recordtime,'%y-%m-%d')=Date_format (Recordtime,'%y-%m-%d'); End;

In the SQL statement, you need to get the insertion time, then find the corresponding value by Timejd the time period and date recordtime, and then add it. As shown in the following:

The left is the real-time insert data, and the right is the trigger update plus. MySQL Live event is described later: http://blog.csdn.net/zlp5201/article/details/38309095

MySQL Trigger drink update operation