mysql触发器学习

 

mysql触发器四要素:

监视地点:监视的哪个表?
监视操作:insert,update,delete?
触发操作:insert,update,delete?
触发时间:after,before?
 
创建触发器语法:
Create trigger triggerName
after/before insert/update/delete on tableName
for each row #mysql此处写死,仅支持行触发,oracle、sql_server支持表触发#
Begin
sql语句 #一句或多句 insert/update/delete语句#
End

 

删除触发器语法:
Drop trigger triggerName;
显示已有触发器语法:
Show triggers
 
 
对insert而言,新插入的行用new来表示,行中每一列的值用new.列名来表示
对于delete而言,删除的行用old来表示,行中每一列的值用old.列名来表示
对于update而言,被修改的行,修改前的数据,用old来表示,old.列名;修改后的数据,用new来表示,new.列名
 
after是先完成数据的增删改,再触发,触发中的语句晚于数据的增删改;
before是先触发再做数据的增删改,触发中的语句先于监视的增删改,我们有机会判断,修改即将发生的操作
 
#deom1添加订单,库存减少(商品库存表g,订单表o)
delimiter $   #定义$为结束符
truncate o$ #清空table o
create trigger tg1
after insert on o
for each row
begin
update  g set num=num-new.much where id=new.gid;
end$
#demo2删除订单,库存增加
create trigger tg2
after delete on o
for each row
begin
update g set num=num+old.much where id=old.gid;
end$
#demo3修改订单数量,库存相应改变
create trigger tg3
after update on o
for each row
begin
update g set num=num+old.much-new.much where id=old.gid;
end$

#demo4 before例子,判断insert 的数据是否合法

drop trigger tg1#删除掉之前的insert触发器,一个action只能有一个触发器
create trigger tg4
before insert on o
for each row
begin
if new.much>5
then  set new.much=5;
end if;
update g set num=num-new.much where id=new.id;
end$

 

mysql 一表多触发器

mysql 一表多触发器,每种触发器只能同时有一个存在,而不能同一种触发器同时有两个存在于一张表。

下面是Update 和 Insert 两种类型的触发器同时存在于一张表的情况:

 

表结构创建sql

-- Table "vtiger_loginhistory" DDL

CREATE TABLE `vtiger_loginhistory` (
  `login_id` int(11) NOT NULL auto_increment,
  `user_name` varchar(25) NOT NULL default ‘‘,
  `user_ip` varchar(25) NOT NULL default ‘‘,
  `logout_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `login_time` timestamp NOT NULL default 0000-00-00 00:00:00,
  `status` varchar(25) default NULL,
  `session_id` varchar(100) default NULL,
  `phone` varchar(10) default NULL,
  `agentstate` tinyint(4) default 4,
  `http_host` varchar(50) default NULL,
  PRIMARY KEY  (`login_id`),
  KEY `logout_time` (`logout_time`),
  KEY `phone` (`phone`),
  KEY `status` (`status`),
  KEY `user_name` (`user_name`),
  KEY `login_time` (`login_time`),
  KEY `status_2` (`status`),
  KEY `user_name_2` (`user_name`),
  KEY `logout_time_2` (`logout_time`),
  KEY `login_time_2` (`login_time`),
  KEY `phone_2` (`phone`),
  KEY `session_id` (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

创建操作触发器

DELIMITER ;;
CREATE TRIGGER `insert_agentstatus_info` AFTER INSERT ON `vtiger_loginhistory` FOR EACH ROW BEGIN
   IF EXISTS(SELECT * FROM vtiger_agentrealstatus WHERE user_name=NEW.user_name ORDER BY  login_id DESC) THEN
  UPDATE vtiger_agentrealstatus SET login_id=NEW.login_id, user_name=NEW.user_name, user_ip=NEW.user_ip, logout_time=NEW.logout_time, login_time=NEW.login_time, status=NEW.status, session_id=NEW.session_id, phone=NEW.phone, agentstate=NEW.agentstate, http_host=NEW.http_host WHERE user_name=NEW.user_name;
     ELSE 
INSERT INTO vtiger_agentrealstatus(login_id, user_name, user_ip, logout_time, login_time, status, session_id, phone, agentstate, http_host)VALUES(NEW.login_id, NEW.user_name, NEW.user_ip, NEW.logout_time, NEW.login_time, NEW.status, NEW.session_id, NEW.phone, NEW.agentstate, NEW.http_host);
    END IF; 
END;;
DELIMITER ;

更新操作触发器

DELIMITER ;;
CREATE TRIGGER `update_agentstatus_info` BEFORE UPDATE ON `vtiger_loginhistory` FOR EACH ROW BEGIN
    IF EXISTS(SELECT * FROM vtiger_agentrealstatus WHERE user_name=NEW.user_name ORDER BY  login_id DESC) THEN
  UPDATE vtiger_agentrealstatus SET login_id=NEW.login_id, user_name=NEW.user_name, user_ip=NEW.user_ip, logout_time=NEW.logout_time, login_time=NEW.login_time, status=NEW.status, session_id=NEW.session_id, phone=NEW.phone, agentstate=NEW.agentstate, http_host=NEW.http_host WHERE user_name=NEW.user_name;
     ELSE 
INSERT INTO vtiger_agentrealstatus(login_id, user_name, user_ip, logout_time, login_time, status, session_id, phone, agentstate, http_host)VALUES(NEW.login_id, NEW.user_name, NEW.user_ip, NEW.logout_time, NEW.login_time, NEW.status, NEW.session_id, NEW.phone, NEW.agentstate, NEW.http_host);
    END IF; 
END;;
DELIMITER ;

 

 

 转自:

http://huaqiang.new.blog.163.com/blog/static/9522124920125101736313/

http://hi.baidu.com/pie138/item/9c0257a847ad8a9a1510730b

mysql触发器学习,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。