MySQL TRIGGER instructions

MySQL TRIGGER) usage instructions
The trigger program is a table-related named database object. This object is activated when a specific event occurs on the table. Mysql 5 and later support triggers. After the trigger is created, the TRG and TRN files (in Linux) are automatically generated in the/var/lib/mysql directory ), you must have sufficient permissions to create a trigger.

Createtrigger <trigger Name>
{Before | after}
{Insert | update | delete}
On <Table Name>
Foreachrow
<Trigger SQL statement>
The parameters are described as follows:
Createtrigger <trigger Name>: Create a New trigger and specify the trigger name, which can contain up to 64 characters.
{Before | after}: used to specify whether to trigger the insert, update, or delete statement before or after the statement is executed.

{Insert | delete | update}: trigger event
On <Table Name>: Specifies the name of the table that responds to the trigger.
Foreachrow: The execution interval of the trigger. foreachrow notifies the trigger to execute an action at every row instead of the entire table.
<Trigger SQL statement>: the SQL statement to be executed by the trigger. If the trigger needs to execute multiple SQL statements, Place multiple statements in begin... In the end block.
For example, begin... End Block

MySQL statement example:
Sqlstr ("
Createtriggeruser_delete
Afterdelete
Onuser
Foreachrow

BEGIN
Deletefromuser_bakwhereid = old. id;
Deletefromaaawhereid = old. id;
END ;");

Create a trigger program under MySQL command line, instance 1:

Delimiter // # change the MySQL command line delimiter to "//" and the default value is ";". In the command line client, if a command line ends with a semicolon, after you press enter, mysql will execute this command.
CreateTRIGGERtestrefbeforeINSERTontest1
Foreachrow

BEGIN
Insertintotest2seta2 = new. a1;
Deletefromtest3wherea3 = new. a1;
Updatetest4setb4 = b4 + 1wherea4 = new. a1;
END //
Delimiter; # restore the default delimiter of the MySQL command line ";"

Example 2:

Droptriggercommon_member_insert; // Delete the trigger if member_update exists.
Delimiter |
Createtriggercommon_member_insert
Afterinsert
On 'member'. 'common _ member'
Foreachrow
Begin
Insertinto 'web '. 'Common _ member' ('uid', 'email ', 'username', 'password) values (new. uid, new. email, new. username, new. password );
End |
Delimiter;
Note: When the common_member data table under the member database is updated, this event is triggered to synchronously update the data to the common_member table under the web database. To put it simply, when the common_member data table in the member database is updated, the common_member table in the web database is synchronously updated.

You can create a trigger in mysql command line mode or by using mysql management software. Common examples include:
1. After SQLyog is successfully connected to the local database, right-click the data table and select "CreateTrigger ..." Or right-click Triggers and choose CreateTrigger ..., I will not repeat it too much here, it is suitable for windows systems)
2. Select a data table-design table-trigger from Navicat. It is applicable to windows systems)

This article is from the redpaopaw blog, please be sure to keep this source http://redpaopaw.blog.51cto.com/7900594/1296745