Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to create MySQL TRIGGER trigger", the content of the article is simple and clear, easy to learn and understand, now please follow the editor's train of thought slowly in depth, together to study and learn "how to create MySQL TRIGGER trigger" bar!

MySQL TRIGGER (trigger)

Database triggers are named database objects maintained in the database and will be activated when the data in the table is modified. Use triggers to improve the functionality and security level of the data in the table. You can use triggers to control access to specific data, perform specific logging, or audit the data itself.

Triggers are named database objects that are activated when table data is modified. They can:

Check and verify deletions and updates before inserting or updating data

Z. acts as a data filter to modify out-of-range data before inserting or updating

Change the behavior of INSERT, UPDATE, and DELETE

For storage engines that do not support foreign keys, mimic the behavior of foreign keys

Z. provides logging function

Automatic creation of summary tables

1.1. Create trigger

CREATE TRIGGER statement:

CREATE TRIGGER trigger_name

{BEFORE | AFTER}

{INSERT | UPDATE | DELETE}

ON table_name FOR EACH ROW

Triggered_statement

Example:

CREATE TRIGGER City_AD AFTER DELETE

ON City FOR EACH ROW

INSERT INTO DeletedCity (ID, Name) VALUES (OLD.ID, OLD.Name)

Trigger_name is the name specified for the trigger, and table_name is the name of the table to be associated with the trigger. BEFORE and AFTER indicate when the trigger was activated (before or after the event was triggered), while INSERT, UPDATE, or DELETE indicate a specific event.

Note: table names OLD and NEW refer to virtual tables that can be viewed by triggers. These tables contain the old version of the data modified by the UPDATE or DELETE statement or the new version of the data added by the INSERT or UPDATE statement, respectively.

1.2. Trigger event

BEFORE and AFTER

The BEFORE and AFTER keywords refer to the activation time of the trigger, relative to the time the change is written to the underlying database relative to the data modification statement (INSERT, UPDATE, or DELETE).

The BEFORE keyword enables the trigger to execute before the data in question is modified. You can use BEFORE triggers to capture invalid data entries and correct or reject them before writing to the table.

-BEFORE INSERT: triggered before new data is added

-BEFORE UPDATE: triggered before updating (or overwriting) existing data with new data

-BEFORE DELETE: triggered before data is deleted

The AFTER keyword defines the trigger that executes after a successful data modification. You can use AFTER triggers to record or audit data changes in the database.

-AFTER INSERT: triggered after new data is added

-AFTER UPDATE: triggered after updating (or overwriting) existing data with new data

-AFTER DELETE: triggered after data is deleted

1.3. Trigger error handling

MySQL handles errors that occur during trigger execution as follows:

If the 0.BEFORE trigger fails, the transaction containing the corresponding row operation will be rolled back.

A. AFTER trigger executes, and AFTER trigger events and row operations must be executed successfully.

For non-transactional tables, transactions are not available. Only statements that trigger the trigger are rolled back.

When the trigger fails, MySQL rolls back the transaction that contains the statement that caused the trigger to fire.

For non-transactional tables, this type of rollback cannot be completed. Therefore, although the statement fails, all changes made before the error occurs will still take effect.

1.4. Check trigger

SHOW CREATE TRIGGER trigger_name

This statement returns a specific string that can be used to recreate the specified trigger. You must know the name of the trigger to run this statement; for SHOW CREATE TRIGGER statements, there is no LIKE or WHERE syntax.

SHOW TRIGGERS

This statement is an MySQL extension. It returns the characteristics of the trigger, such as database, name, type, creator, and date of creation and modification. This statement has the advantage of displaying specific triggers based on the conditions provided in the LIKE schema or the WHERE clause. If no condition is specified, this statement displays information for all triggers.

INFORMATION_SCHEMA.TRIGGERS:

This statement contains all the data displayed by the SHOW command and completely renders the triggers available in all databases.

1.5. Delete trigger

Use the following syntax to explicitly delete a trigger:

DROP TRIGGER [IF EXISTS] [schema_name.] trigger_name

When using DROP TRIGGER trigger_name, the server looks for the name of the trigger in the current mode. If you want to delete triggers in other schemas, include the schema name. Use IF EXISTS to prevent errors that occur when trying to delete triggers that do not exist. Triggers can be implicitly deleted by deleting the table for which the trigger is defined or the database that contains the trigger

1.6. Restrictions on triggers

Statements that are not allowed include:

L SQL preprocessing statement

L show or imply COMMIT and ROLLBACK

A statement that returns a result set, for example, a SELECT statement that does not contain an INTO var_list clause

L FLUSH statement

The statement used to modify the table to which the trigger is to be applied

L SHOW statement

The following changes do not trigger the trigger:

Changes caused by cascading foreign keys

L changes caused during row-based replication

Process the result set in the trigger by using SELECT... INTO var_list, or by using cursors and FETCH statements.

1.7. Trigger permissions

To execute the CREATE TRIGGER and DROP TRIGGER commands, you need TRIGGER permission.

The permissions required to use OLD and NEW in triggers: to use SET NEW.col_name = value to assign values to a column, you need to have UPDATE permission on the column; to use NEW.col_name in an expression to reference a new column value, you need to have SELECT permission on the column.

Thank you for your reading, the above is the content of "how to create MySQL TRIGGER trigger". After the study of this article, I believe you have a deeper understanding of how to create MySQL TRIGGER trigger, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.