mysql触发器 实例_mysql触发器的三个例子

本节内容：

mysql触发器实例

例1，

复制代码 代码示例:

delimiter //

create trigger InsertUser

before insert on user

for each row

Begin

insert into user_group(uid,gid) values(new.uid,'group4444444444');

end;//

delimiter ;

例2，

复制代码 代码示例:

delimiter //

create trigger InsertUser

before insert on user

for each row

Begin

IF new.Type=2 then

insert into user_group(uid,gid) values(new.uid,'group4444444444');

else

insert into user_group(uid,gid) values(new.uid,'group55555555555')

END IF;

end;//

delimiter ;

例3，

复制代码 代码示例:

delimiter //

create trigger InsertUser

before insert on user

for each row

Begin

IF new.type=1 then

insert into user_group(uid,gid) values(new.uid,'578d3369633b47bd9c1fe8bf905cbfb1');

END IF;

IF new.type=2 then

insert into user_group(uid,gid) values(new.uid,'387bcd57fc5a4c3c9de83ee210fef661');

END IF;

end;//

delimiter ;

mySql触发器实例

在mysql中，使用触发器能进行一些约束。

例子，当Student表的StudentID列被发生更改时,BorrowStudent表的StudentID列也跟着更改.如果Student表删除某记录,BorrowStudent也删除对应StudentID的记录.

复制代码 代码示例:

/*先删除将要创建而存在的表*/

drop table if exists Student;

drop table if exists BorrowStudent;

/*创建表*/

create table Student(

StudentID int not null primary key,

StudentName varchar(30) not null,

StudentSex enum('m','f') default 'm'

)engine=myisam;

create table BorrowStudent(

BorrowRecord int not null auto_increment primary key,

StudentID int not null,

BorrorDate date,

ReturnDate date,

foreign key(StudentID) references Student(StudentID)

)engine=myisam;

/*插入记录*/

insert into Student values(1235412,'java','m');

insert into Student values(3214562,'jiajia','m');

insert into Student values(5441253,'purana','f');

insert into BorrowStudent(StudentID,BorrorDate,ReturnDate)

values(1235412,'2007-01-01','2007-01-07');

insert into BorrowStudent(StudentID,BorrorDate,ReturnDate)

values(3214562,'2007-01-01','2007-01-07');

insert into BorrowStudent(StudentID,BorrorDate,ReturnDate)

values(5441253,'2007-01-01','2007-01-07');

/*创建触发器*/

delimiter $$

drop trigger if exists tduStudent$$

drop trigger if exists tddStudent$$

create trigger tduStudent before update

on Student for each row

begin

if new.StudentID!=old.StudentID then

update BorrowStudent

set BorrowStudent.StudentID=new.StudentID

where BorrowStudent.StudentID=old.StudentID;

end if;

end$$

create trigger tddStudent before delete

on Student for each row

begin

delete

from BorrowStudent

where BorrowStudent.StudentID=old.StudentID;

end$$

delimiter ;