MySql触发器实例

「爱情、让人受尽委屈。」 2022-09-18 09:48 233阅读 0赞

触发器能进行一些约束.  
这是个小例子,当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 ;