MYSQL5: Trigger

What is a trigger

MySQL triggers (trigger), like stored procedures, are a program embedded in MySQL. Triggers are events that trigger an action that includes insert, UPDATE, and DELETE statements. If a trigger is defined, the trigger is fired when the database executes the statement, which is the named database object associated with the table, which is activated when a specific event occurs on the table.

Create a Trigger

The trigger is a special stored procedure, but the execution of the stored procedure is invoked using the call statement, and the execution of the trigger does not need to be invoked with the call statement, nor does it need to be started manually, as long as a predefined event occurs, it is automatically called by MySQL . For example, the operation of the student table (INSERT, delete, or update) is activated when it is executed.

Triggers can query other tables and can contain complex SQL statements. They are primarily used to meet complex business rules or requirements. You can create a trigger that has only one statement, but it is usually more than a trigger that has multiple statements executed, even if the trigger of a single statement can be written in the same way as a trigger of multiple statements, to see the basic wording of a trigger that has more than one execution statement:

TRIGGER trigger_name trigger_time trigger_event for each ROW trigger_stmt  

Explain:

1, trigger_name identify trigger name, user-specified

2, trigger_time identification trigger time, can be specified as before or after

3. Trigger_event identifies trigger events, including INSERT, UPDATE, and delete

4. Tbl_name identifies the name of the table that established the trigger, that is, on which table the trigger is established

5, TRIGGER_STMT is the trigger program body, the trigger program can use begin and end as the beginning and end, the middle contains more than one statement

The trigger program can use begin and end as the start and end, with multiple statements in the middle. For example, a previous student table:

Table Student (    studentid            int                 nullvarchar (nullintvarchar (15 ))

Create a trigger table for the Studentname, Studentage, Studentphone Three fields of the student table:

Table triggerstudentname (    VARCHAR);  Tableint);  TableVARCHAR ();          

Create a trigger that interpolates the fields to three tables at a time after inserting one piece of data:

CREATEtrigger trigger_student after insert on Studentfor each ROW begin insert into Triggerstudentname valuesinsert into triggerstudentage values (New.studentage); insert into triggerstudentphone values (New.studentphone)                 

Insert three data:

Insertinto studentValuesNull‘Jack‘,‘11‘,‘55555555‘);Insertinto studentValuesNull‘dicky "" 14 ",  66666666 '  ' 19 ' 

Take a look at the three-sheet situation:

No problem, the execution results show that, while inserting data into the student table, the data in Triggerstudentname, Triggerstudentage, and triggerstudentphone three tables is mounted, The INSERT action triggers the trigger.

View triggers

Viewing triggers refers to the definition, status, and syntax information of a trigger that already exists in the database. You can view the triggers you've created by using commands, and there are two ways to see the triggers, one for each.

1. Show triggers statement view trigger

The statements for viewing triggers through show triggers are as follows:

SHOW TRIGGERS;

Use this command to view the trigger:

Part of it does not intercept the whole, explain the meaning of the main part:

(1) trigger represents the name of the trigger, where two triggers are tri_student and trigger_student, respectively.

(2) The event represents the events that activate the trigger, where the two triggering events are insert operations

(3) Table for the action object that activates the trigger, which is the student table

(4) statement represents the statement executed after activating the trigger

(5) Timing indicates the trigger trigger time, respectively, before the insert operation (before) and after the insert operation (after)

2. View the trigger information in the Triggers table

The show triggers statement looks at all the trigger information that is currently being created, which is handy if there are fewer triggers, and if you want to view specific trigger information, you can find it directly from the triggers table in the Infomation_schema database. Viewed through the Select command, the basic syntax is:

WHERE condition;

Like what:

'trigger_student';

You can check the effect of the command run yourself.

Delete Trigger

Using the DROP TRIGGER statement, you can delete a trigger that has already been defined in MySQL, and the basic syntax for deleting a trigger is:

[schema_name. ]trigger_name;

Schema_name represents the database name, which is optional, if schema_name is omitted, the trigger is removed from the current database, Trigger_name is the name of the trigger to be deleted, such as:

TRIGGER school.tri_student

Trigger Tri_student Delete Succeeded

Note points for using triggers

When using triggers, it is important to note that for the same table, only one trigger can be created for the same event .

For example, if you create a before insert trigger on a table student, if you create a before insert trigger on the table student again, MySQL will get an error, and you can only create after on table student Insert or before trigger of the update type. The flexibility to use triggers will save a lot of trouble for the operation.

MYSQL5: Trigger