MySQL trigger, mysql trigger instance

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