MySQL Study Notes 4-triggers and MySQL views

The MySQL trigger creates only one trigger to execute the statement:

Create trigger name BEFORE | after trigger event

ON Table name for each row execution statement creation trigger with multiple execution statements

Basic Form:

Create triger trigger name BEFORE | AFTER trigger event

ON Table name FOR EACH ROW

BEGIN

Execution Statement List

END note: in MySQL, only one trigger can be created for a table at the same time and at the same trigger time. For example, if an INSERT event is triggered, only one trigger can trigger AFTER, however, BEFORE triggers can be defined. View the trigger show triggers; SELECT * FROM information_schema.triggers;

Or: SELECT * FROM information_schema.triggers WHERE TRIGGER_NAME = 'trigger name ';

Information_schema is the default database in MySQL that records trigger information. Application triggers
A trigger cannot contain keywords such as start transcation, COMMIT, or ROLLBACK, or CALL statements. Delete trigger

Drop trigger name

The MySQL view is a virtual table that is exported from one or more tables in the database. Its content is defined by the query. The database only stores view definitions, but does not Store View data. The data in the view depends on the data in the original table. A view is an SQL statement stored in a database. It is mainly used for security and makes complex queries easy to understand and use. View function: simple security logic Data independence create view to check whether the user has the permission to create a view

Select select_priv, create_view_priv from mysql. user where user = 'username ';

Create View

CREATE [ALGORITHM = {UNDEFINDE | MERGE | TEMPTABLE}]

VIEW name [(attribute list)]

As select statement

[WITH [CASCADED | LOCAL] check option]; Note: You have the permission to create a view. The select statement cannot contain subqueries in the from clause; the select statement cannot reference the system or user variables. The select statement cannot reference the parameters of the pre-processing statement. The definition cannot reference the sub-program parameters or the tables or views referenced by local variables in the definition must exist. The temporary table cannot be referenced in the definition, and the temporary view cannot be created. The tables named in the view definition must exist. The triggers and views cannot be associated with order by in the view definition. However, if you select from a specific view, and the view uses a statement with its own order by, it will be ignored to view the view DESCRIBE/DESC view name; show table status like 'view name '; modify the view create or replace [ALGORITHM = {UNDEFINDE | MERGE | TEMPTABLE}]

VIEW [(attribute list)]

As select statement

[WITH [CASCADED | LOCAL] check option] Although data in the ALTER update view can be updated, there are many restrictions. Generally, it is better to use a view as a virtual table for Data Query instead of updating data through the view. Delete view drop view if exists <VIEW Name> [RESTRICT | CASCADE]