MySQL database Advanced (v)--triggers

MySQL database Advanced (v)--trigger one, Introduction to triggers 1, Introduction to Triggers

A trigger is a special stored procedure associated with a table that triggers execution when data is inserted, deleted, or modified in a table, with finer and more complex data control than the standard functionality of the database itself.

2, the advantages of the trigger

A, security
You can make a user have some right to manipulate the database based on the value of the database. You can limit user actions based on time, such as not allowing changes to database data after work and holidays, or restricting user actions based on data in the database, such as the price of a stock that is not allowed to increase by more than 10% at a time.
B, Audit
You can track user actions on a database.??? A statement that audits the user's operations database, and writes the user's updates to the database to the audit table.
C. Implementing complex data integrity rules
Implement non-standard data integrity checks and constraints. Triggers can produce more complex restrictions than rules. Unlike rules, triggers can reference columns or database objects. For example, a trigger can rewind any futures that attempt to eat more than their own margin. Provides a variable default value.
D, the implementation of complex non-standard database-related integrity rules.
Triggers can be used for serial updates of related tables in the database.
?? Cascade Modify or delete rows in other tables that match them when modified or deleted.
?? Sets the row in the other table to a null value when modified or deleted.
Sets the row cascade in the other table to the default value when modified or deleted.
?? Triggers can reject or roll back changes that disrupt related integrity and cancel transactions that attempt to update data.
E, synchronously replicate the data in the table in real time.
F, SQL triggers provide another way to run scheduled tasks. The data values are calculated automatically, and if the values of the data meet certain requirements, specific processing is performed. For example, if the company's account has less than $50,000 in money, it immediately sends warning data to the financial officer.

3. Limitations OF Triggers

A, the trigger cannot invoke the stored program that returns the data to the client, nor can it use a dynamic SQL statement that takes a call statement, but allows the stored program to return data to the trigger via parameters, that is, if the stored procedure or function returns the data to the trigger via an out or inout type parameter, However, the process of returning data directly cannot be called.
B. Statements, such as Start trans-action,commit or rollback, cannot be used in a trigger to start or end a transaction in a display or an implicit manner.

Ii. use of triggers 1, creating triggers

Syntax for creating triggers:
CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt
Trigger_name: The name of the trigger.
Tirgger_time: Trigger time, for before or after.
Trigger_event: Trigger event for INSERT, delete, or update.
Tb_name: Represents the name of the table on which the trigger is established and the one on which the trigger is established.
TRIGGER_STMT: The program body of a trigger, which can be an SQL statement or multiple statements that are contained with begin and end.
The for every row indicates that the action on any one record satisfies the triggering event and triggers the trigger.
In addition to defining the insert, UPDATE, and delete basic operations, MySQL also defines the load data and replace statements, which can also trigger triggers of the above 6 types.
The load data statement is used to load a file into a data table rather than a series of insert operations.
The Replace statement is generally similar to the INSERT statement, except that when you have a primary key or a unique index in the table, if the data you insert is identical to the original primary key or unique index, the original data is deleted and then a new piece of data is added.
Insert trigger: Activates a trigger when inserting a row, triggered by the insert, LOAD DATA, and replace statements;
UPDATE trigger: Activates a trigger when a row is changed, triggered by an UPDATE statement;
Delete trigger: Activates a trigger when a row is deleted, triggered by a delete, replace statement.
Variable declaration:
DECLARE var_name[,...] type [DEFAULT value]
A SET statement is used to assign values to variables, with the following syntax:
SET var_name = expr [,var_name = expr] ...
New and old are defined in MySQL, which represents the row of data that triggers the trigger in the same table as the trigger.
In an INSERT trigger, new is used to represent the before or already (after) inserted data;
In an update trigger, old is used to represent the original data that will or has been modified, and new is used to represent the data that will or has been modified;
In a delete type trigger, old is used to denote the original data that will or has been deleted;
How to use: New.columnname (ColumnName is a column name for the corresponding data table)
In addition, old is read-only, and new can use set assignment in a trigger without triggering the trigger again, causing the loop to be called.

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 is discarded from the current database. Trigger_name is the name of the trigger to be deleted.

3, Trigger Information view

In MySQL, all triggers are defined in the triggers table of the INFORMATION_SCHEMA database and can be viewed through the query command select, with the following syntax:
SHOW TRIGGERS [FROM schema_name];

Execution order of triggers
InnoDB database, if SQL statement or trigger execution fails, MySQL rolls back the transaction with:
A, if the before trigger fails, SQL does not execute correctly.
B, the After type trigger does not fire when SQL execution fails.
C, after-type trigger execution fails, SQL is rolled back.
MySQL triggers are executed in the order of before triggers, row operations, after triggers, where an error in either step does not continue, and if an operation is performed on the transaction table, if an error occurs, it is rolled back and cannot be rolled back if the non-transactional table is manipulated , the data may be faulted.

Third, trigger application 1, the realization of business logic

Customers place orders to order goods, the commodity table automatically reduce the quantity.
Create a delete trigger on the product table, delete an item, and automatically delete the order for that item.
Create a product table with a product number, product name, product quantity, and product price of four columns, where the product number is from the growth column and is set to 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 order table with three columns, order number, product number, and quantity, where the order number is self-growing and 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(‘桃子‘, 100, 2);insert into product(pname, pnum, price)values(‘苹果‘, 80, 8);insert into product(pname, pnum, price)values(‘香蕉‘, 50, 5);

Create a trigger on the order form, and when there is an order, the quantity of the product is automatically reduced based on the product number and quantity of the order. The trigger in new 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 a two order

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

View the product table to see a reduction in the corresponding number of products. The operation is done by the INSERT trigger on the order table.

Create a new trigger on the order form, when the order of a product product quantity is greater than the product inventory, prohibit placing orders, that is, prohibit inserting records in the order table.
There can be only one insert type trigger in a table, first delete the insert trigger.
drop trigger trigger_order;
MySQL cannot cancel operations in a trigger by rolling back a transaction, but if an error occurs during the execution of a trigger's SQL statement, the operation is automatically undone and the curve implements 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<NEW.onum    THEN  SELECT XXXX INTO mesg;ELSE    UPDATE product SET pnum=pnum-NEW.onum where pid=NEW.pid;END IF; END

Insert the order to see if the inventory is still able to insert successfully if not enough.
INSERT INTO orders(pid, onum)VALUES(1, 110);

2. Achieve security

A, limit the date when the record is inserted
An INSERT trigger is created on the order form, and orders are not allowed on Sunday, Saturday.

create trigger trigger_limitDateBEFORE INSERT ON orders FOR EACH ROWBEGINDECLARE mesg varchar(10);IF DAYNAME(now())=‘sunday‘ or DAYNAME(now())=‘saturday‘    THEN SELECT XXXXX INTO mesg;ELSE    SET mesg=‘允许插入订单‘;END IF;END

Verify that the trigger created above is working correctly, and see if the current time is Saturday Sunday, insert a record into the order table to check for success.
insert into orders(pid,onum) values (3,30)
B. Limit the scope of data changes
Create an update trigger on the product table to limit the price of a product to an increase of 20%.
The trigger is set to before update, which checks whether the price increase is more than 20% before the change, and if it is exceeded, causes an error and cancels the operation.
The update operation is divided into two steps, the first step is to delete the original record, and the second step is to insert a new record. The original record is in the old table, and the new record is in the newer table. New.price in the trigger is the new price, Old.price is the original price.

create trigger trigger_limitIncreasePriceBEFORE UPDATE ON product FOR EACH ROWBEGINDECLARE mesg varchar(10);if (NEW.price-OLD.price)*100/OLD.price > 20    then select XXXX into mesg;else    set mesg=‘更改成功‘;end if;END

Validating triggers
update product set price=20 where pid=1;

3. Achieve data integrity

Use triggers to limit the range of values that a table can insert into a column.
Create a student table with four columns, name, gender, phone, and mailbox.

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

A, the value range of the specified sex column
Create triggers, restrict sex columns, and allow only "male" and "female" input. The Before insert trigger, which does not meet the condition, executes an incorrect SQL statement and exits.

create trigger trigger_limitSexbefore insert on personinfo for each rowbegindeclare mesg varchar(10);if NEW.sex=‘男‘ or NEW.sex=‘女‘ then   set mesg=‘更改成功‘;else    select xxxx into mesg;end if;End

Validating triggers

insert into personinfo VALUES(‘孙悟空‘, ‘难‘,‘18900000000‘);insert into personinfo VALUES(‘唐僧‘, ‘男‘,‘18900000001‘);

B, limit the type and length of cell phone column
Create a trigger that allows only the phone column to enter a 11-digit number, 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=‘插入成功‘;else    select xxxx into mesg;end if;End

Validation triggers, if the phone column inserts a value that is incorrect or the first bit is not 1, the insertion will fail.
insert into personinfo VALUES(‘唐僧‘, ‘男‘,‘2890000001‘);

4. Using Trigger auditing

Use triggers to track personinfo table data operations and log trace 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 Personinfo table one record
insert into personinfo values(‘孙悟空‘, ‘男‘, ‘13008080808‘);
View additional 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=‘孙悟空‘;
View the added delete record 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 with the name ' Monkey King ' in the Personinfo table.
update personinfo set phone=‘189080808‘ where sname=‘孙悟空‘;
View added update records in the Reivew table

MySQL database Advanced (v)--triggers