MySQL Trigger _ MySQL

Introduction to MySQL Trigger 1

The database server is passive in nature. We use an SQL statement to display the requirement, and it will execute an operation. A trigger is to program a passive server as an active server.

A trigger is a piece of code stored in the directory that contains procedural and declarative statements. if a specific operation is executed on the database and only when a certain condition is set, the database server activates the database, which has more refined and complex data control capabilities than the standard functions of the database itself.

There are many similarities between triggers and stored procedures, that is, they are stored in a directory as a procedural database object and are composed of declarative and procedural SQL statements. But there is an important difference between the two concepts, that is, the call method is different, the trigger cannot be called from a program or stored procedure, there is no call or execute trigger similar statements. MySQL calls it transparently without being noticed by users.

2 Advantages and disadvantages of triggers
2.1 Advantages

2.1.1 Automatic. The trigger is automatically processed when data changes. for programmers, the trigger is invisible, but it does.
2.1.2 Security. There is no SQL statement injection problem. you can restrict the user's right to operate the database based on the database value.
For example, you can restrict user operations based on time. for example, you cannot modify database data after work or holidays.
User operations can be restricted based on the data in the database. for example, the stock price cannot be increased by more than 10% at a time.
2.1.3 audit. You can track your database operations.
For example, statements used to audit user operations on databases.
Write updates to the database into the audit table.
2.1.4 The business logic is encapsulated and easy to modify.
2.1.5 data integrity.
2.1.5.1 implement complex data integrity rules
Implements non-standard data integrity checks and constraints. Triggers can generate more complex limits than rules. Unlike rules, triggers can reference columns or database objects. For example, a trigger can be used to roll back any attempt to eat futures that exceed its deposit.
Provides variable default values.
2.1.5.2 implement complex non-standard database integrity rules. Triggers can be used to update related tables in the database.
The trigger can reject or roll back the changes that undermine the integrity of the transaction and cancel the transaction trying to update the data. If you update five tables, the database server goes down when the two tables are updated. the database server will automatically roll back after the restart.

2.2 Disadvantages
2.2.1 poor portability.
2.2.2 timed triggering and cannot be called.
2.2.3 when data is exported and imported between databases, unnecessary triggering logic may occur.
2.2.4 fewer and fewer beginners are proficient in SQL.

3. create a trigger

3.1 Creation syntax:

CREATE TRIGGER trigger_name    trigger_time trigger_event ON tbl_nameFOR EACH ROWtrigger_stmt

Trigger_time indicates the time when the program is triggered. It can be BEFORE or AFTER to indicate that the trigger program is triggered BEFORE or AFTER its statement is activated.
Trigger_event indicates the type of statements used to activate the trigger program. It can be INSERT, UPDATE, or DELETE.
Trigger_stmt is the statement executed when the trigger program is activated. If you want to execute multiple statements, you can use BEGIN... END
Statement structure.

Create trigger
 <触发器名称>
  
{Before | after} {insert | update | delete} on
  <表名>
   
For each row
   <触发器sql语句>
  
 

Note: As shown in the preceding figure, six triggers can be created: before insert, before update, before delete, after insert, after update, and after delete.
Another restriction is that two triggers of the same type cannot be created on a table at the same time. Therefore, a maximum of six triggers can be created on a table.

3.2 Example: two tables are jointly updated.

Create Test table 1

drop table if exists tab_first;create table tab_first(tab_first_id varchar(11));

Create Test table 2

drop table if exists tab_sec;create table tab_sec(tab_sec_id varchar(11));

Create a trigger

drop trigger if exists tri_afterinsert_tab_first;delimiter //create trigger tri_afterinsert_tab_firstafter insert on tab_firstfor each rowbegin    insert into tab_sec(tab_sec_id) values (new.tab_first_id);end//delimiter ;

3.3 note:

A trigger can call a stored procedure.

4. view the trigger

View the basic information of all stored triggers in the database, including the database, trigger name, creation time, and creation statement.

show triggers /G

Or

SELECT * FROM information_schema.triggers  WHERE trigger_name='tri_afterinsert_tab_first' /G

5. trigger

5.1 trigger mode

The trigger can be triggered if the trigger meets the predefined trigger conditions.

5.2 for example, the corresponding records are automatically added to the tab_sec table after the tab_first table record is added.

mysql> insert into tab_first(tab_first_id) values ("test1");mysql> select * from tab_first;+--------------+| tab_first_id |+--------------+| test1        |+--------------+mysql> select * from tab_sec;+------------+| tab_sec_id |+------------+| test1      |+------------+

Note:

6. modify the stored procedure

6.1 Basic syntax

Alter procedure stored PROCEDURE name [characteristic...]

Note: characteristic is the feature of the stored PROCEDURE when it is created. it has been introduced in the create procedure statement. As long as the values are set, the features of the stored procedure change accordingly.
If you want to modify the content of a stored procedure, you can delete it first and then redefine the stored procedure.Some features of stored procedures are as follows:
Characteristic:
COMMENT 'string'
| LANGUAGE SQL
| {Contains SQL | no SQL | reads SQL data | modifies SQL DATA}

| SQL SECURITY {DEFINER | INVOKER}

6.2 for example: modifying features

View default feature values

mysql> SELECT SPECIFIC_NAME,SQL_DATA_ACCESS, ROUTINE_COMMENT FROM information_schema.Routines WHERE ROUTINE_NAME='procedureAdd';+---------------+-----------------+-----------------+| SPECIFIC_NAME | SQL_DATA_ACCESS | ROUTINE_COMMENT |+---------------+-----------------+-----------------+| procedureAdd  | CONTAINS SQL    |                 |+---------------+-----------------+-----------------+

Change the read/write permission to reads SQL DATA, and add the comment 'this is a test! ', The code is executed as follows:

mysql> ALTER  PROCEDURE  procedureAdd READS SQL DATA COMMENT 'This is a test!';

View the modified feature value

mysql> SELECT SPECIFIC_NAME,SQL_DATA_ACCESS, ROUTINE_COMMENT FROM information_schema.Routines WHERE ROUTINE_NAME='procedureAdd';+---------------+-----------------+-----------------+| SPECIFIC_NAME | SQL_DATA_ACCESS | ROUTINE_COMMENT |+---------------+-----------------+-----------------+| procedureAdd  | READS SQL DATA  | This is a test! |+---------------+-----------------+-----------------+

Note: the query result shows that the permission to access DATA (SQL _DATA_ACCESS) has changed to reads SQL DATA, and the function annotation (ROUTINE_COMMENT) has changed to "This is a test! ".

7. delete a stored procedure

7.1 Basic syntax

Drop procedure if exists stored PROCEDURE name

7.2 for example

DROP PROCEDURE  IF  EXISTS procedureAdd;

Note: You cannot delete another stored procedure in one stored procedure. you can only call another stored procedure.

**************************************** **************************************** **************************************** ***************************************

8. Stored procedure local variables

8.1 Introduction

You can declare local variables within a stored procedure. They can be used to store intermediate temporary results. If we need a local VARIABLE in a stored procedure, we must use the declare variable statement to introduce it. Through declaration, the data type of the variable is determined, and the initial value can be specified. (If declare variable statements are used, they must be included in the first statement of the BEGIN-END statement block)

8.2 for example:

delimiter //create procedure test(out num1 integer) begin     declare num2 integer default 100;     set num1 = num2; end//delimiter ;

Call stored procedure

call test(@num);select @num;

9 stored procedures and user variables

9.1 Introduction

User variables always have a global feature. even if they are created within a stored procedure, they are retained after the stored procedure ends. User variables created outside the stored procedure can still retain their own values in the stored procedure.

9.2 for example

delimiter //create procedure user_variable() begin     set @varTest = 1; end//delimiter ;

After calling the stored procedure, check that the value of varTest is 1.

call user_variable();select @varTest;

Note: The set statement is a part of SQL itself. it can assign a value to user variables and local variables, or use any random expression.

10 stored procedure and cursor

10.1 Introduction

The conventional select statement may return multiple rows. you can use cursor to process this and import one row of data to the stored procedure. Four special statements are required to use a cursor: declare sursor, open sursor, fetch cursor, and close cursor.

If you declare a cursor using the declare cursor statement, we connect it to a table expression. Next, you can use the fetch cursor statement to obtain the generated result from a row to the stored procedure. At a certain time point, only one row is visible in the result, that is, the current row. It is like an arrow pointing to a row in the result. this is also the name of the cursor. Using the fetch cursor statement, we can move the cursor to the next row. after processing all rows, we can use the close cursor statement to delete the result.

10.2 cursor functions and attributes

Purpose:
It is used to traverse the records returned by the query database for corresponding operations;
Attribute:
The cursor is read-only, that is, it cannot be updated;
Cursors cannot be rolled, that is, they can only be traversed in one direction. they cannot freely move between records or skip some records;
Avoid updating data on tables with opened cursors.

10.3 how to use a cursor

Declared cursor

DECLARE cursor_name cursor for select statement;

Open cursor

OPEN cursor_name;

Move cursor

FETCH cursor_name INTO variable list;  

Close cursor

CLOSE cursor_name;  

10.4 cursor instance

Create Test tables and data

CREATE TABLE test.users (    ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,    user_name varchar(60) NOT NULL DEFAULT '',    user_pass varchar(64) NOT NULL DEFAULT '',    PRIMARY KEY (ID)   )ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

insert into test.users values(1,'name1', 'pass1');insert into test.users values(2,'name2', 'pass2');insert into test.users values(3,'name3', 'pass3');insert into test.users values(4,'name4', 'pass4');insert into test.users values(5,'name5', 'pass5');

Create a cursor stored procedure

delimiter |create procedure test_cursor (in param int(10),out result varchar(90))begin    declare name varchar(20);    declare pass varchar(20);    declare done int;    declare cur_test CURSOR for select user_name,user_pass from test.users;    declare continue handler FOR SQLSTATE '02000' SET done = 1;    if param then        select concat_ws(',',user_name,user_pass) into result from test.users where id=param;    else        open cur_test;        repeat            fetch cur_test into name, pass;            select concat_ws(',',result,name,pass) into result;            until done end repeat;        close cur_test;    end if;end;|delimiter ;

Detailed explanation of each line of commands

One line tells the MySQL interpreter that the input end command is changed to |, and the default value is; (the command itself has nothing to do with the stored procedure) two lines create a stored procedure. note: if I change out result varchar (90) to out result varchar, only one character is returned. 3 rows, 4 rows starting, define a variable name 5 rows, define the variable pass 6 rows, define an end identifier 7 rows, define a cursor, point to select user_name, user_pass from test. users; statement 8 rows. if sqlstate is equal to 02000, set done to 1, that is, when data cannot be found, rows 9, 11, 18, and if are judged as 10 rows. based on the parameter, extract the data and place it in the result. the concat_ws function indicates concat with separator, which has a separator string connection. for example, after the connection, 12 rows are separated by commas (,). open the cursor for lines 13 and 16 and repeat loops, the root php do while has 14 rows in the same principle and extracts data from the cursor. 15 rows: merge 17 rows of data, close the cursor for 18 and 19 rows, and close the tag. 20 rows,

Result feedback

mysql> call test_cursor(3,@test);                                               Query OK, 1 row affected (0.00 sec)mysql> select @test;+-------------+| @test       |+-------------+| name3,pass3 |+-------------+1 row in set (0.00 sec)mysql> call test_cursor('',@test);Query OK, 1 row affected, 2 warnings (0.00 sec)mysql> select @test;+-------------------------------------------------------------------------+| @test                                                                   |+-------------------------------------------------------------------------+| name1,pass1,name2,pass2,name3,pass3,name4,pass4,name5,pass5,name5,pass5 |+-------------------------------------------------------------------------+1 row in set (0.00 sec)

<关键字：trigger delimiter>

Reprinted please indicate the source: http://blog.csdn.net/jesseyoung/article/details/34826721