mysql触发器学习

浏览数：45 / 时间：2015年06月12日

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 ;