mysql 创建触发器 记录

今天把mysql中创建触发器的知识记录下来.以备后用！

create table Employee_message(Eno char(12) not null,Epassword char(20) not null,Edepart char(10) not null,
Ename char(10) not null,Etel char(13) not null,primary key(Eno,Edepart));

create table Route_message(Rno char(12) not null,Rstart char(20) not null,Rend char(20) not null,Rpass
Text not null,RNum int not null check(RNum>=2),primary key(Rno));

create table Car_message(Cno char(12) not null,Rno char(12) not null,Ctype char(20) not null,Cts char(10) not null,
Cnumber int not null check(RNum>=2),CplateNum char(20) not null,primary key(Cno),foreign key(Rno) references
 Route_message(Rno));

//销售表

create table Ticket_sale_message(Tno char(12) not null, Rno char(12) not null,Tprice double not null,TworkTime datetime not null,
TlastTime int not null check(TlastTime>=1),Eno char(12) not null,primary key(Tno),foreign key(Rno) references
 Route_message(Rno),foreign key(Eno) references Employee_message(Eno));

//存储表
create table Ticket_save_message(Trno char(12) not null, TallNum int not null check(TallNum>=0),ToverNum int not null
 check(ToverNum<=TallNum and ToverNum>=0), primary key(Trno),foreign key(Trno) references Route_message(Rno)
 on delete cascade on update cascade);

预先创建了这几个表.希望Ticket_sale_message插入一个数据，Ticket_save_message的ToverNum就能减少1；插入一个数据，就能增加一，同时保证剩余票数是正确的。

于是创建了如下触发器;

/* 建立触发器 */
DROP TRIGGER IF EXISTS Ticket_over_insert;
DELIMITER $
create trigger Ticket_over_insert after insert on Ticket_sale_message
for each row
begin
update Ticket_save_message set ToverNum=ToverNum-1 where new.Rno=Trno;  /* 保证自减  */
update Ticket_save_message set ToverNum=TallNum-(select count(*) from Ticket_sale_message where new.Rno=Trno)where new.Rno=Trno;/* 确保ToverNum的正确性 */
END$
DELIMITER;


DROP TRIGGER IF EXISTS Ticket_over_delete;
DELIMITER $
create trigger Ticket_over_delete after delete on Ticket_sale_message
for each row
begin
update Ticket_save_message set ToverNum=ToverNum+1 where old.Rno=Trno;/*   保证自加*/
update Ticket_save_message set ToverNum=TallNum-(select count(*) from Ticket_sale_message where old.Rno=Trno)where old.Rno=Trno;/* 确保ToverNum的正确性 */
END$
DELIMITER;

然后插入几个数据开始验证触发器。

/*test 触发器t*/
insert into Employee_message values('E001','root','销售','xxx','13157704450');
insert into Route_message values('R001','cd','sn','dj',3);
insert into Ticket_save_message values('R001',10,9);
select * from Ticket_save_message;

insert into Ticket_sale_message values('T001','R001',13,'2017-10-22 17:41',1,'E001');

select * from Ticket_sale_message;

select * from Ticket_save_message;

此时ToverNum数值并没有减少.而是修复了插入数值的错误，

update Ticket_save_message set ToverNum=10 where Trno='R001';

delete from Ticket_sale_message;
select * from Ticket_save_message;

此时ToverNum并没有自增，愉快的达到了目的。

最后得出结论，触发器能成功实现我希望的功能。希望以后还能对自己有用！