MySql触发器实例
来源:互联网 发布:苹果手机赌博软件 编辑:程序博客网 时间:2024/05/21 17:25
触发器能进行一些约束.
这是个小例子,当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 ;
这是个小例子,当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 ;
- MySQL触发器实例(记录触发器)
- Mysql触发器实例
- Mysql 触发器实例
- mysql触发器实例
- MySQL 触发器简单实例
- MySQL 触发器简单实例
- MySQL 触发器简单实例
- MySQL 触发器简单实例
- mysql触发器成功实例
- MySQL 触发器简单实例
- MySQL 触发器简单实例
- mysql 触发器实例
- MySql触发器实例
- MySQL 触发器简单实例
- MySQL 触发器简单实例
- MySQL 触发器简单实例
- mysql 触发器实例
- MySQL 触发器简单实例
- DNS子域委派
- UNIX IPC -----FIFO 实现
- ASP.NET利用showModalDialog來做一個父子視窗互相傳值的功能
- 丑数分解
- Javascript_16_DOM_form练习
- MySql触发器实例
- 很好的文章,没时间转了,把网址记下,关于linux驱动开发的
- 关于JSP和Servlet的一些回顾认识
- SQL, PL/SQL 之NUMBER数据类型
- 程序员面试——走台阶问题
- 索引跳跃扫描
- MonteDB资源3
- 从完全不包含人体的图片中随机剪裁出64*128大小的用于人体检测的负样本
- 硬件总线学习之路_USB总线体系协议简介