MySQL Getting started is simple-triggers

1, triggers are events that trigger an action that includes an INSERT statement, an UPDATE statement, and a DELETE statement. When the database system executes these events, the trigger is activated to perform the appropriate action. MySQL supports triggers starting from 5.0.2. Using triggers ensures consistency between certain operations.

2. Create a trigger that executes a statement

Create TRIGGER Trigger name Before|after trigger event on table name for each row execution statement;

The Before and after parameters specify the time the trigger executes;

Trigger events include INSERT, update, and delete;

The for every row indicates that a trigger is triggered by an operation on any record that satisfies the triggering condition;

Execution statement refers to a program executed after a trigger is triggered

Create trigger DEPT_TRIG1 before insert on Department for each row insert to Trigger_time values (now ());

3. Create multiple triggers to execute statements

Create TRIGGER Trigger name Before|after trigger event on table name for each row begin EXECUTE statement list end;

In general, MySQL defaults to '; ' Ends the execution statement. This symbol is required during the creation of the trigger. also use '; ' in the Execution statement list Separate execution statements, so you can use delimiter statements.

Delimiter &&

Create Trigger Dept_trig2 after delete

On Department for each row

Begin

INSERT into trigger_time values (' 21:01:01 ');

INSERT into trigger_time values (' 22:01:01 ');

End

&&

Delimiter

In MySQL, only one trigger can be created for the same trigger event in a table at the same trigger time. If the trigger event is insert, there is only one trigger with a trigger time of after.

4. View triggers

Refers to information such as the definition, status, and syntax of a trigger that already exists in the database. The view method includes the show triggers statement and the query triggers table under the INFORMATION_SCHEMA database, and so on.

Show triggers \g//View all triggers, not suitable for many cases of triggers

SELECT * from Information_schema.triggers;

SELECT * from information_schema.triggers where trigger_name= ' trigger name ';

5. In MySQL, the order in which triggers are executed is before triggers, table operations (INSERT, update, and delete), and after triggers

Create trigger Before_insert before insert on Department for each row insert into trigger_test values (null, "Before_insert ");

Create trigger After_insert before insert on Department for each row insert into trigger_test values (null, "After_insert") ;

When activating a trigger, there are some restrictions on the execution statement of the trigger. For example, a trigger cannot contain a start transaction, commit, or rollback keyword and cannot contain a call statement.

During the execution of the trigger, any step error prevents the program from going down, and for normal tables, the updated records cannot be rolled back, and the updated data will remain in the table.

6. Delete Trigger

Drop trigger trigger name.

Trigger Name parameter if the specified name finds the trigger under the current database, delete it if it is found. If you specify a database, the database system goes down to the specified database to find the trigger.

If you no longer need a trigger, be sure to delete the trigger.

MySQL Getting started is simple-triggers