触发器能进行一些约束.

这是个小例子,当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 ;