-- 主表 user_permission 在user_permission1,user_permission2,user_permission3表都有user_permission表的iosBalance冗余字段
-- 修改user_permission表的iosBalance字段,这三张表user_permission1,user_permission2,user_permission3的iosBalance也要修改
-- 所以用触发器写,只要user_permission表的iosBalance字段修改了,就触发这个触发器
DELIMITER $
CREATE TRIGGER user_permission AFTER UPDATE ON user_permission FOR EACH ROW
BEGIN
update user_permission1 set iosBalance=new.iosBalance where new.userId;
update user_permission2 set iosBalance=new.iosBalance where new.userId;
update user_permission3 set iosBalance=new.iosBalance where new.userId;
END $
DELIMITER ;
DROP TABLE IF EXISTS tri_mrm_models_status;
CREATE TABLE tri_mrm_models_status (
id int(11) NOT NULL AUTO_INCREMENT COMMENT '自增长ID',
material_representation_models_id bigint(20) NOT NULL COMMENT 'material_representation_models.id',
d_creator_id bigint(20) NOT NULL COMMENT '所属代理商ID',
material_representation_id bigint(20) NOT NULL COMMENT 'material_representation.id',
material_model_id bigint(20) NOT NULL COMMENT 'material_model.id',
old_price decimal(11, 2) comment '旧,市场价格',
new_price decimal(11, 2) NOT NULL comment '新,市场价格',
old_discount decimal(11, 2) comment '旧,缺省价格',
new_discount decimal(11, 2) NOT NULL comment '新,缺省价格',
old_d_stock int(11) comment '旧,库存',
new_d_stock int(11) NOT NULL comment '新,库存',
old_customize_model varchar(31) comment '旧,商家SKU',
new_customize_model varchar(31) NOT NULL comment '新,商家SKU',
old_weight decimal(7,2) comment '旧,重量',
new_weight decimal(7,2) NOT NULL comment '新,重量',
old_unit varchar(15) comment '旧,单位',
new_unit varchar(15) NOT NULL comment '新,单位',
old_specification varchar(255) comment '旧,规格',
new_specification varchar(255) NOT NULL comment '新,规格',
remark varchar(32) comment '备注商品操作来源',
updateby int(11) COMMENT '录入人',
updated varchar(20) NOT NULL COMMENT '录入时间',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='material_representation_models.status log表';
-- 修改
drop trigger IF EXISTS tri_mrm_models_status_update;
DELIMITER $$
CREATE TRIGGER tri_mrm_models_status_update AFTER update ON material_representation_models FOR EACH ROW
BEGIN
if (old.price<>new.price or old.discount<> new.discount or old.d_stock<>new.d_stock or
old.customize_model<>new.customize_model or old.weight<>new.weight or old.unit<>new.unit or
old.specification<>new.specification
) then
INSERT INTO tri_mrm_models_status (
material_representation_models_id,
d_creator_id,
material_representation_id,
material_model_id,
old_price,
new_price,
old_discount,
new_discount,
old_d_stock,
new_d_stock,
old_customize_model,
new_customize_model,
old_weight,
new_weight,
old_unit,
new_unit,
old_specification,
new_specification,
remark,
updateby,
updated)
VALUES(
old.id,
old.d_creator_id,
old.material_representation_id,
old.material_model_id,
old.price,
new.price,
old.discount,
new.discount,
old.d_stock,
new.d_stock,
old.customize_model,
new.customize_model,
old.weight,
new.weight,
old.unit,
new.unit,
old.specification,
new.specification,
new.remark,
new.updateby,
date_format(now(),'%Y-%m-%d %H:%i:%S')
);
end if;
END $$
DELIMITER ;
-- 修改
drop trigger IF EXISTS tri_mrm_models_status_add;
DELIMITER $$
CREATE TRIGGER tri_mrm_models_status_add AFTER insert ON material_representation_models FOR EACH ROW
BEGIN
INSERT INTO tri_mrm_models_status (
material_representation_models_id,
d_creator_id,
material_representation_id,
material_model_id,
old_price,
new_price,
old_discount,
new_discount,
old_d_stock,
new_d_stock,
old_customize_model,
new_customize_model,
old_weight,
new_weight,
old_unit,
new_unit,
old_specification,
new_specification,
remark,
updateby,
updated)
VALUES(
new.id,
new.d_creator_id,
new.material_representation_id,
new.material_model_id,
0,
new.price,
0,
new.discount,
null,
new.d_stock,
null,
new.customize_model,
0,
new.weight,
null,
new.unit,
null,
new.specification,
new.remark,
new.updateby,
date_format(now(),'%Y-%m-%d %H:%i:%S')
);
END $$
DELIMITER ;
rigger_name:标识触发器名称,用户自行指定;
trigger_time:标识触发时机,取值为 BEFORE 或 AFTER;
trigger_event:标识触发事件,取值为 INSERT、UPDATE 或 DELETE;
tbl_name:标识建立触发器的表名,即在哪张表上建立触发器;
trigger_stmt:触发器程序体,可以是一句SQL语句,或者用 BEGIN 和 END 包含的多条语句。
INSERT 型触发器:插入某一行时激活触发器,可能通过 INSERT、LOAD DATA、REPLACE 语句触发;
UPDATE 型触发器:更改某一行时激活触发器,可能通过 UPDATE 语句触发;
DELETE 型触发器:删除某一行时激活触发器,可能通过 DELETE、REPLACE 语句触发。
old 数据库旧值
new 数据库新值
删除触发器
drop trigger IF EXISTS tri_mrm_models_status_add;
NEW 与 OLD 详解
上述示例中使用了NEW关键字,和 MS SQL Server 中的 INSERTED 和 DELETED 类似,MySQL 中定义了 NEW 和 OLD,用来表示
触发器的所在表中,触发了触发器的那一行数据。
具体地:
在 INSERT 型触发器中,NEW 用来表示将要(BEFORE)或已经(AFTER)插入的新数据;
在 UPDATE 型触发器中,OLD 用来表示将要或已经被修改的原数据,NEW 用来表示将要或已经修改为的新数据;
在 DELETE 型触发器中,OLD 用来表示将要或已经被删除的原数据;
使用方法: NEW.columnName (columnName 为相应数据表某一列名)
另外,OLD 是只读的,而 NEW 则可以在触发器中使用 SET 赋值,这样不会再次触发触发器,造成循环调用(如每插入一个学生前,都在其学号前加“2013”)。