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;