日期:2014-05-16  浏览次数:20632 次

mysql 创建触发器-例子
业务逻辑:表myupload做了增加、修改、删除时,在表myuploaddaybook新增一条用来记录myupload表变化的行。

myupload表
CREATE TABLE `myupload` (
  `uploadid` int(11) NOT NULL AUTO_INCREMENT,
  `bookid` int(11) DEFAULT NULL,
  `numb` int(11) DEFAULT NULL,
  `saleuserid` int(11) DEFAULT NULL,
  `trdate` varchar(20) DEFAULT NULL,
  `state` char(1) DEFAULT NULL,
  `remark` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`uploadid`)
) ENGINE=InnoDB AUTO_INCREMENT=856 DEFAULT CHARSET=utf8;


myuploaddaybook表
CREATE TABLE `myuploaddaybook` (
  `uploadid` int(11) NOT NULL AUTO_INCREMENT,
  `bookid` int(11) DEFAULT NULL,
  `numb` int(11) DEFAULT NULL,
  `saleuserid` int(11) DEFAULT NULL,
  `trdate` varchar(20) DEFAULT NULL,
  `mng` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`uploadid`)
) ENGINE=InnoDB AUTO_INCREMENT=856 DEFAULT CHARSET=utf8;


before insert
create trigger mydb_insert_trigger
before insert On myupload                      
for each row                                         
insert into myuploaddaybook(bookid,saleuserid,numb,trdate,mng) 
values(new.bookid,new.saleuserid,new.numb ,new.trdate,'insert')


before Update
Create Trigger mudb_update_trigger
before Update On myupload                      
for each row 
insert into myuploaddaybook(bookid,saleuserid,numb,trdate,mng) 
values(old.bookid,old.saleuserid,new.numb ,new.trdate,'update')


before delete
create trigger mydb_del_trigger
before delete On myupload                      
for each row                                         
insert into myuploaddaybook(bookid,saleuserid,numb,trdate,mng) 
values(old.bookid,old.saleuserid,old.numb ,old.trdate,'delete')



------------------------------
查看触发器:
show triggers;


删除触发器:
drop TRIGGER mydb_del_trigger;