MySQL trigger, mysql trigger instance
MySQL versions 5.0.2 and later support triggers. A trigger has some time with commands to trigger certain operations.Events include insert statements, delete statements, and update statements..Triggers can be used to record database operations.
1. Create a mysql trigger:
(1) create a trigger with a single execution statement
Create trigger name before | after trigger event
On Table name for each row execution statement
Before | after: Specifies the trigger execution time.
Foreach row: indicates that the trigger event is triggered when the operation on any record is met.
Example:
Create a table:
Create table timelog (
Id int (11) primary key auto_increment,
Savetime varchar (50) not null,
Saveinfo varchar (50) not null
);
Create a saveTimeTrigger trigger:
Delimiter //
Create trigger saveTimeTrigger before insert
On studentinfo for each row
Insert into timelog (savetime) values (now ());
//
Before you insert data to the studentinfo table, the database automatically inserts the current operation time to timelog.
More: http://hovertree.com/menu/mysql/
(2) create a trigger with multiple execution statements
Create trigger name before | after trigger event
On Table name for each row
Begin
List of executed statements
End
For example:
Delimiter //
Create trigger saveTimeTrigger before insert
On studentinfo for each row
Begin
Insert into timelog (savetime) values (now ());
Insert into timelog (saveinfo) values ('insert ');
End
//
2. view trigger commands
Showtriggers;
Select * from information_schema.triggers where TRIGGER_NAME = 'trigger name ';
Information_schema.triggers: a data table used to record trigger information in the database;
TRIGGER_NAME: used to specify the name of the trigger to be viewed
3. delete a trigger
Droptrigger trigger name;
Recommended: http://www.cnblogs.com/roucheng/p/mysqlhanshu.html