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

MySQL Database Advanced (5)-- trigger 1, trigger introduction 1, trigger introduction

Triggers are special stored procedures associated with tables that can trigger execution when inserting, deleting, or modifying data in the table, and have finer and more complex data control capabilities than the standard functions of the database itself.

2. Advantages of flip-flop

A, security

You can give the user some right to operate the database based on the value of the database. Users' actions can be limited based on time, such as not allowing database data to be modified after work and holidays, and based on data in the database, for example, stock prices are not allowed to rise by more than 10% at a time.

B, audit

You can track the user's actions on the database. Audit the statements that the user manipulates the database; write the user's updates to the database to the audit table.

C. Implement complex data integrity rules

Implement non-standard data integrity checks and constraints. Triggers can create more complex restrictions than rules. Unlike rules, triggers can reference columns or database objects. For example, triggers can roll back any futures that attempt to eat more than their own margin. Provides a variable default value.

D. implement complex non-standard database-related integrity rules.

Triggers can make serial updates to the relevant tables in the database.

Cascade modifies or deletes matching rows in other tables when modifying or deleting.

Set the matching rows in other tables to null values when modified or deleted.

Cascade matching rows in other tables to default values when modified or deleted.

Triggers can reject or roll back changes that undermine the integrity of the relevant changes and cancel transactions that attempt to update data.

E. copy the data in the table synchronously and in real time.

F, SQL triggers provide another way to run scheduled tasks. Automatically calculate the data value, if the value of the data meets certain requirements, then carry out specific processing. For example, if the amount of money in the company's account is less than 50,000 yuan, send warning data to the financial staff immediately.

3. Restrictions on triggers

A. the trigger program cannot call the stored program that returns the data to the client, nor can it use the dynamic SQL statement with CALL statement, but it allows the stored program to return the data to the trigger through parameters, that is, it is possible for the stored procedure or function to return the data to the trigger through parameters of OUT or INOUT type, but it cannot call the procedure that returns the data directly.

B. you cannot use statements that start or end a transaction in an explicit or implicit way, such as START TRANS-ACTION,COMMIT or ROLLBACK, in a trigger.

Second, the use of triggers 1. Create triggers

Syntax for creating triggers:

CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt

Trigger_name: name of the trigger.

Tirgger_time: trigger time: BEFORE or AFTER.

Trigger_event: trigger event, which is INSERT, DELETE, or UPDATE.

Tb_name: indicates the name of the table on which the trigger is created, and on which table the trigger is created.

Trigger_stmt: the program body of a trigger, which can be a SQL statement or multiple statements contained in BEGIN and END.

FOR EACH ROW indicates that any action on a record that satisfies the trigger event will trigger the trigger.

In addition to defining the basic operations of INSERT, UPDATE and DELETE, MySQL also defines LOAD DATA and REPLACE statements, which can also cause triggers of the above 6 types.

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 similar to INSERT statements, except that when there is an primary key or unique index in a table, if the inserted data is the same as the original primary key or unique index, the original data will be deleted first, and then a new data will be added.

Insert trigger: trigger is activated when a row is inserted, and is triggered by INSERT, LOAD DATA, and REPLACE statements

Update trigger: activate a trigger when a row is changed, triggered by a UPDATE statement

Delete trigger: activates the trigger when a row is deleted, which is triggered by DELETE and REPLACE statements.

Variable declaration:

DECLARE var_name [,...] Type [DEFAULT value]

Use the SET statement to assign variables, and the syntax is as follows:

SET var_name = expr [, var_name = expr].

NEW and OLD are defined in MySQL to represent the row of data in the table where the trigger is triggered.

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

In the update trigger, OLD is used to represent the original data that will or has been modified, and NEW is used to represent the 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

Usage: NEW.columnName (columnName is a column name of the corresponding data table)

In addition, OLD is read-only, while NEW can use SET assignment in the trigger without triggering the trigger again, causing a circular call.

2. Delete trigger

DROP TRIGGER [IF EXISTS] [schema_name.] trigger_name

Schema_name is the name of the database and is optional. If schema is omitted, the trigger will be discarded from the current database. Trigger_name is the name of the trigger to be deleted.

3. Check the trigger information

In MySQL, the definitions of all triggers exist in the triggers table of the INFORMATION_SCHEMA database, which can be viewed through the query command SELECT. The syntax is as follows:

SHOW TRIGGERS [FROM schema_name]

Execution order of triggers

InnoDB database. If the SQL statement or trigger fails, MySQL rolls back the transaction, such as:

A. if the execution of the BEFORE trigger fails, the SQL cannot execute correctly.

B, when SQL execution fails, after trigger will not trigger.

The execution of triggers of type C and AFTER fails and SQL rolls back.

MySQL triggers are executed in the order of BEFORE triggers, row operations, and AFTER triggers. If an error occurs in any of these steps, the rest of the operation will not be carried out. If there is an error in the operation on the transaction table, it will be rolled back. If the operation on the non-transaction table is performed, then it cannot be rolled back and the data may be wrong.

Third, trigger application 1, realize business logic

When the customer places an order to order the goods, the goods list automatically reduces the quantity.

Create a delete trigger in the item table, delete an item, and automatically delete the order for that item.

Create a product table with four columns: product number, product name, product quantity and product price, in which the product number grows from the column and is set as the primary key.

Create table product (pid int PRIMARY KEY AUTO_INCREMENT,pname VARCHAR (10), price DOUBLE,pnum INT) ENGINE=innoDB default CHARSET=utf8

Create an order table with three columns, order number, product number, and quantity, where the order number grows from the column and is set as the primary key.

Create table orders (oid INT PRIMARY KEY AUTO_INCREMENT,pid INT,onum INT) ENGINE=innoDB DEFAULT CHARSET=utf8

Insert three products, product name and quantity, and price.

Insert into product (pname, pnum, price) values ('Peach', 100,2); insert into product (pname, pnum, price) values ('Apple', 80,8); insert into product (pname, pnum, price) values ('Banana', 50,5)

Create a trigger on the order table, and when there is an order, the number of products will be automatically reduced according to the product number and quantity of the order. The NEW in the trigger represents a table that holds the inserted order record.

Create trigger trigger_orderAFTER INSERT ON orders FOR EACH ROWBEGINUPDATE product SET pnum=pnum-NEW.onum where pid = NEW.pid;END

Insert two orders

INSERT INTO orders (pid, onum) VALUES (1,10); INSERT INTO orders (pid, onum) VALUES (2,5)

Looking at the product table, you can see the corresponding reduction in the number of products. The operation is done by the Insert trigger of the order table.

Create a new trigger on the order table that forbids placing an order when the quantity of a product ordered is greater than the product inventory, that is, prohibiting the insertion of records in the order table.

There can be only one trigger of type INSERT in a table. Delete the INSERT trigger first.

Drop trigger trigger_order

MySQL cannot cancel the operation by rolling back the transaction in the trigger, but if an error occurs during the execution of the SQL statement of the trigger, the operation will be automatically undone and the curve will realize the transaction rollback.

Create trigger trigger_orderBEFORE INSERT ON orders FOR EACH ROWBEGINDECLARE var int;DECLARE mesg varchar (20); SELECT pnum INTO var FROM product where pid=NEW.pid;IF var 20 then select XXXX into mesg;else set mesg=' changed successfully'; end if;END

Verification trigger

Update product set price=20 where pid=1

3. Realize data integrity

Use triggers to limit the range of values that a table inserts into a column.

Create a student table with four columns, name, gender, mobile phone and mailbox.

Create table personinfo (sname VARCHAR (5), sex CHAR (1), phone VARCHAR (11)) ENGINE=innoDB default CHARSET=utf8

A. specify the value range of the gender column

Create a trigger, restrict the column, and only "male" and "female" are allowed. Before insert trigger, do not meet the condition to execute the error SQL statement, exit.

Create trigger trigger_limitSexbefore insert on personinfo for each rowbegindeclare mesg varchar (10); if NEW.sex=' male'or NEW.sex=' female 'then set mesg=' changed successfully; else select xxxx into mesg;end if;End

Verification trigger

Insert into personinfo VALUES ('Sun WuKong', 'difficult', '1890000000001'); insert into personinfo VALUES ('Tang monk', 'male', '18900000001')

B. Limit the value type and length of the mobile phone column

To create a trigger, only the mobile number entered in the phone column can only be 11 digits, and the first digit is 1.

Create trigger trigger_limitPhonebefore insert on personinfo for each rowbegindeclare mesg varchar (10); if NEW.phone regexp'[1] [0-9] {10} 'then set mesg=' inserted successfully'; else select xxxx into mesg;end if;End

Verify the trigger, if the number of digits inserted in the phone column is incorrect or the first digit is not 1, the insert will fail.

Insert into personinfo VALUES ('Tang Monk', 'male', '2890000001')

4. Audit using triggers

Use triggers to track the data operations of the personinfo table and record the tracking events to an audit table review.

Create table review (username VARCHAR (20), action VARCHAR (10), studentID CHAR (5), sname CHAR (10), actionTime TIMESTAMP)

A. create trigger record insert operation

Create trigger trigger_insertbefore insert on personinfo for each rowbegininsert into review values (user (), 'insert',new.sname,now ()); End

Insert a record in the personinfo table

Insert into personinfo values ('Sun WuKong', 'male', '13008080808')

View the added INSERT records in the review table

Select * from review

B. create trigger record delete operation

Create trigger trigger_deleteafter DELETE on personinfo for each rowbegininsert into review values (user (), 'delete',old.sname,now ()); End

Delete a record from personinfo

Delete from personinfo where sname=' Sun WuKong'

View the added DELETE records in the reivew table

C. Create trigger record modification operation

Create trigger trigger_updateafter UPDATE on personinfo for each rowbegininsert review values (user (), 'update',new.sname,now ()); End

Update the phone named 'Sun WuKong' in the personinfo table.

Update personinfo set phone='189080808' where sname=' Sun WuKong'

View the added UPDATE records in the reivew table

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.