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

Trigger is a special stored procedure, the difference is that the stored procedure is called by CALL, while the trigger does not need to use CALL or manual start, as long as a predefined event occurs, it will be automatically called by MYSQL. It triggers execution when inserting, deleting, or modifying data in a particular table, and it has finer and more complex data control capabilities than the standard functions of the database itself. It is a kind of database object related to table operation. When a specified event occurs on the table where the trigger is located, the object will be called, that is, the execution of the trigger on the table's action event trigger publication.

Create a trigger:

The syntax for creating a trigger is as follows:

CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW tigger_stmt

Where:

Trigger_name: identify the trigger name, customized by the user

Trigger_time: identifies the timing of the trigger, with a value of BEFORE or AFTER to indicate that the trigger is triggered before or after the statement that activates it

Trigger_event: identifies the trigger event. The value is INSERT,UPDATE or DELETE

Tbl_name: identifies the name of the table on which the trigger is created, that is, on which table the trigger is created

Trigger_stmt: trigger program body, which can be a sql statement or multiple statements contained in BEGIN and END. A trigger is a named database object associated with a table that is activated when a specific event occurs on the table. The trigger is associated with a table named tbl_name. Tbl_name must reference the persistence table.

Thus, we can set up six kinds of triggers, namely BEFORE INSERT,BEFORE UPDATE,BDFORE DELETE,AFTER INSERT,AFTER UPDATE,AFTER DELETE. In addition, we cannot create two triggers of the same type on the same table, so we can create up to six triggers on one table.

Let's take a closer look at trigger_event:

In addition to defining the basic operation of INSERT,UPDATE,DELETE, mysql also defines LOAD DATA and REPLACE statements, which can also trigger the above six types of triggers.

The LOAD DATA statement is used to load a file into a data table, which is equivalent to a series of INSERT operations.

Generally speaking, REPLACE statements are very similar to INSERT statements, except that when there is an primary key or unique index in a table, if the inserted data is consistent with the original primary key and unique indexes, the original data will be deleted first, and then a new data will be added, that is to say, an REPLACE statement is sometimes equivalent to an INSERT statement, and sometimes equivalent to a DELETE statement plus an INSERT statement.

Trigger_event indicates the type of statement that activates the trigger, which can be one of the following values:

INSERT: activates the trigger program when a new row is inserted into the table, for example, through INSERT,LOAD DATA, and REPLACE statements.

UPDATE: activates the trigger when you change a line, for example, through a UPDATE statement.

DELETE: activates the trigger when a row is deleted from the table, for example, through DELETE and REPLACE statements. It is important to note here that trigger_event is not very similar to sql statements that activate trigger programs in a table operation. For example, the BEFORE trigger for INSERT can be activated not only by the INSERT program, but also by the LOAD DATA statement.

Delete trigger:

DROP TRIGGER [schema_name.] trigger_name

If the trigger program is discarded above, the scheme name (schema_name) is optional, and if the scheme is omitted, the trigger will be discarded from the current solution. Super permission is required to discard trigger program statements.

We mentioned the BEGIN...END statement above, and let's talk about their usage:

In mysql, the syntax of the BEGIN...END statement is:

BEGIN

[statement_list]

END

Among them, statement_list represents a list of one or more statements, and each statement in the list must be used; to end, and in mysql, the semicolon is the identifier of the end of the statement, and the semicolon indicates that the mysql can start execution as soon as the statement ends, so the interpreter starts execution after encountering the semicolon in statement_list, and then reports an error because the END matching with BEGIN is not found. At this point, the DELIMITER (meaning of delimiter, delimiter) command is used, which is a command that does not require the end of the statement flag. The syntax is:

DELIMITER new_delemiter

Where new_delemiter is one or more length symbols, the default is the semicolon, we can change it to other symbols $: DELIMITER $. After that, the statement ends with a semicolon, and the interpreter will not react until it encounters $. Note that after using it, we have to change it back.

Let's look at a specific example of using BEGIN...END:

Next, let's look at an example of creating a trigger:

Let's assume that there are two tables in the system:

Class table: class (class number classID, number of students in the class stuCount)

Student form: student (student number stuID, class number calssID)

We want to create a trigger to automatically update the number of students in the class table as students are added, as follows:

DELIMITER $

Create trigger tri_stuInsert after insert

On student for each row

Begin

Declare c int

Set c = (select stuCount from class where classID=new.classID)

Update class set stuCount = where classID 1 where classID = new.classID)

End$

DELIMITER

Let's talk about the variables above. In mysql, DECLARE is used to define a local variable that can only be used in BEGIN...END compound statements and should be defined at the beginning of the compound statement, that is, before other statements. The syntax is: declare var_name [...] type [DEFAULT value]. Where var_name is the variable name, like the sql statement, the variable name is case-insensitive, type is any data type supported by mysql, and multiple variables of the same type can be defined at the same time, separated by commas, and the initial value of the variable is NULL. If needed, you can use the DEFAULT clause to provide the default value, and the value can be an expression. Set statement is used to assign variables, and the syntax is: set val_name=expr []...

We also used the NEW keyword in the above example, so let's talk about the NEW and OLD keywords:

In mysql, they are used to represent the row of data that triggered the trigger in the table where the trigger is located.

In INSERT triggers, NEW is used to represent new data that will be (BEFORE) or has been (AFTER) inserted.

In update triggers, OLD is used to represent data that will or has been modified, and NEW is used to represent new data that will 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 is: new.columnName (columnName is a corresponding column name)

In addition, old is read-only, while new can use set assignment in the trigger so that the trigger is not triggered again, causing a circular call (for example, adding "2016" before each student is inserted).

View triggers:

Viewing triggers refers to the definition, status, syntax and other information of triggers that already exist in the database. We can use SHOW TRIGGERS and view trigger information in the TRIGGERS table.

(1) SHOW TRIGGERS

(2) View the trigger information in the TRIGGERS table of the information_schema database:

Select * from 'information_schema'.'TRIGGERS' where' TEIGGER_NAME'='ins_sum'

The above is the basic operation of the flip-flop that I have handed over at present. With the in-depth understanding in the future, I will further learn the flip-flop.

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.