/ 中存储网

MySQL触发器使用教程

2014-07-13 15:40:06 来源:中存储网

查询数据库中是否存在触发器
SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_STATEMENT
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA='dbname'; -- 'dbname' 数据库名称
-------------------------
一、创建触发器 triggerName(名称)
CREATE TRIGGER triggerName
AFTER/BEFORE  INSERT/DELETE/UPDATE
ON tableName
FOR EACH ROW
BEGIN
sql语句(inert/delete/update范围内)
END$
二、删除触发器
DROP TRIGGER triggerName
三、实例一 场景:当产生订单是订单表新增记录,相应的商品表减少商品数量
3.1 创建goods(商品)表和t_order(订单)表
create table goods (
id int(7) primary key auto_increment,
goods_name varchar(10),
goods_count int(6)
)charset utf8;
create table t_order (
order_id int(4) primary key auto_increment,
goods_id int(4),
order_count int(6)
)charset utf8;

3.2插入测试数据到goods表
insert into goods (goods_name,goods_count) values ('图书',24),('铅笔',108),('橡皮擦',45);
3.3 触发器实际中的作用:监视数据库的某种操作并触发某种操作
3.3.当产生订单的时候,商品表中的商品数相应减少
监视t_order表的插入操作:insert into t_order (goods_id,order_count) values (1,10);
触发goods表的更新操作:update goods  set goods_count = goods_count - new.order_count where goods_id = new.goods_id;
完整语法
create trigger insertAfterDelete
after t_order on insert
for each row
begin
update goods  set goods_count = goods_count - new.order_count where goods_id = new.goods_id;
end$
-------------------------------------------------------------------------
四实例二 (数据库item,商品表goods,商品价格监控表goods_price_monitor)
场景:当商品价格发生变化时,记录到商品监控表内

1,创建商品表
create table goods( 
 num_iid bigint(20) not null,
 outer_id varchar(60),
 title varchar(100),
 modified datetime,
 jdp_modified datetime,
 price  decimal(14,2) not null COMMENT '商品价格',
 num int(5) not null ,
 approve_status varchar(32),
 nick varchar(32),
 created datetime,
 detail_url varchar(255),
 pic_url varchar(255)
 PRIMARY KEY(num_iid),
 KEY `items_modified` (modified),
 KEY `items_jdp_modified` (jdp_modified),
 KEY `items_outer_id` (outer_id)
)ENGINE=MYISAM  DEFAULT  CHARSET=utf8  COMMENT "商品表"

2,创建商品价格监控表
CREATE TABLE `goods_price_monitor` (
 `id` int(10) NOT NULL auto_increment,
 `outer_id` varchar(64) default NULL,
 `num_iid` bigint(20) NOT NULL,
 `title` varchar(100) default NULL,
 `old_price` decimal(14,2) NOT NULL COMMENT '商品原价格',
 `new_price` decimal(14,2) NOT NULL COMMENT '商品新价格',
 `old_status` varchar(32) default NULL COMMENT '商品状态 onsale出售中 instock库中',
 `new_status` varchar(32) default NULL COMMENT '最新商品状态 onsale出售中 instock库中',

  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=28 DEFAULT CHARSET=utf8 COMMENT='商品价格监控表'


3,检查数据库item是否存在触发器
SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_STATEMENT
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA='item';


4,创建触发器price_monitor


CREATE TRIGGER price_monitor
AFTER  UPDATE
ON goods
for each ROW
BEGIN
 IF(old.price <> new.price)THEN
 INSERT INTO price_monitor (num_iid,outer_id,title,old_price,new_price,old_status,new_status,uptime)
  VALUES(old.num_iid,
   old.outer_id,
   old.title,
   old.price ,
   new.price ,
   old.approve_status,
   new.approve_status,
   NOW());
 END if;
END



总结;
触发器可以更新远程的表,但远程的表必须使用FEDERATED存储引擎。
  复制相关:
 触发器在MySQL复制环境中也是可以工作的。
  在MySQL5.0的版本和其他大多数数据库系统一样,触发器和复制都能工作。
在主库上,通过触发器承载的操作,不会复制到从库上。
但是,如果主库上存在的触发器,如果在从库上的相关表中也创建的话,在从库上的触发器就能和主库一样被激活、触发。
  在主库上的操作是怎样被复制到从库上去的?
  首先要确定一件事:主从库都有同样的触发器,在主库上创建的触发器也要在从库上重新创建一遍。
 这样DML语句在复制中就能在从库上激活触发器。
例如:还是a表,我们在a表中创建了一个after insert 触发器,复制过程如下:
  1).一条insert语句插入a表。
  2).after insert 激活触发器,也插入到b表。
  3).insert语句写入到bin log中。
  4).复制线程获得并执行insert语句。
  5).after insert 激活触发器,也插入到b表。