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;

评论

1 楼 hu3625067 2013-04-10  

写的太棒了，受益匪浅！！！