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

I. trigger

TRIGGER is one of the database objects of MySQL and has been supported since version 5.0.2. This object is very similar to a function in a programming language, requiring declaration, execution, and so on. However, the execution of the trigger is not called by the program, nor started by hand, but triggered and activated by events to achieve execution. It's a bit like an event in DOM.

Triggers are triggered by events, which include add, delete, and change operations. When the database performs these operations, the trigger is activated to perform the corresponding actions.

Syntax for creating triggers CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmttrigger_name: trigger name tirgger_time: trigger execution event BEFORE: trigger AFTER before event: trigger event after event trigger_event: trigger event INSERT: activate trigger when a row is inserted, INSERT,LOAD DATA,REPLACE statement can trigger UPDATE: activate trigger when a row is changed The UPDATE statement can trigger DELETE: activate the trigger when a row is deleted, and the DELETE,REPLACE statement can trigger tb_name: which table is to be executed by the trigger FOR EACH ROW: trigger trigger_stmt: the program body of the trigger at a frequency of once per row It can be a SQL statement or multiple statements contained in BEGIN and END 1. Create a trigger with only one execution statement: CREATE TRIGGER trigger name BEFORE | AFTER trigger event ON table name FOR EACH ROW execution statement 2. Create a trigger with multiple execution statements: CREATE TRIGGER trigger name BEFORE | AFTER trigger event ON table name FOR EACH ROWBEGIN execution statement list END

By default, MySQL conflicts with the required branches in the list of trigger execution statements as the closing symbol. To solve this problem, you can use DELIMITER, such as DELIMITER |, to change the ending symbol to | |.

DELIMITER | | CREATE TRIGGER trigger name BEFORE | AFTER trigger event ON table name FOR EACH ROWBEGIN execution statement list END | | DELIMITER;3, NEW and OLD

NEW and OLD are defined in MySQL to record what has changed, specifically:

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

UPDATE:OLD is used to represent the original data that will be or has been modified, and NEW is used to represent the new data that will or has been modified.

DELETE:OLD is used to represent the original data that will or has been deleted.

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

4. Example

Create a user (users) table, a logs table

CREATE TABLE `users` (`id` int (20) not null auto_increment primary key, `name` varchar (255)); CREATE TABLE `logs` (`nid` int (20) not null auto_increment primary key, `log` varchar (255))

Create a trigger (record a log for each user added)

Delimiter / / create trigger user_log after insert on users for each rowbegininsert into logs (log) values (new.name); end / / delimiter

Use triggers

The trigger cannot be called directly by the user, but it is caused by the passive add / delete / change operation on the table.

Insert into users (name) values ('admin')

View trigger

SHOW TRIGGERS statement to view trigger information

Delete trigger

The function of drop trigger user_log;5 and trigger

Database triggers have the following functions:

1. 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.

User operations can be restricted based on data in the database, for example, stock prices are not allowed to rise by more than 10% at a time.

two。 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.

3. 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.

4. Implement complex non-standard database-related integrity rules. Triggers can make serial updates to the relevant tables in the database. For example, a delete trigger on the author_code column of the auths table can cause matching rows in other tables to be deleted accordingly.

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. This trigger works when a foreign key that does not match its primary key is inserted. For example, you can generate an insert trigger on a books.author_code column, and if the new value does not match a value in the auths.author_code column, the insert is rolled back.

5. Copy the data in the table synchronously and in real time.

6. 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.

The function of trigger is quoted from https://www.cnblogs.com/cuiliang/p/4900181.html.

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.