mysql触发器的三个例子

delimiter //   
create trigger InsertUser   
before insert on user  
for each row   
Begin  
insert into user_group(uid,gid) values(new.uid,'group4444444444');   
end;//   
delimiter ;  

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 ; 

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 ;

/*先删除将要创建而存在的表*/
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 ;