mysql 触发器

– 卡表插入触发器

DELIMITER $$

CREATE

TRIGGER `card_insert_trigger` AFTER INSERT ON `card` 
FOR EACH ROW BEGIN
-- 插入数据(卡表id非关联字段不做插入)
INSERT INTO `fed_card`(

card_batch,
card_cardsort_id,
card_agent_id,
card_merchant_id,
card_no,
card_pwd,
card_password,
card_time,
card_assigntime,
card_member_id,
card_usetime,
card_pay,
card_freeze,
card_usetype
) VALUES (
new.card_batch,
new.card_cardsort_id,
new.card_agent_id,
new.card_merchant_id,
new.card_no,
new.card_pwd,
new.card_password,
new.card_time,
new.card_assigntime,
new.card_member_id,
new.card_usetime,
new.card_pay,
new.card_freeze,
new.card_usetype
);

END;

$$

DELIMITER ;

– 卡表更新触发器

DELIMITER $$

CREATE
TRIGGER card_update_trigger AFTER UPDATE ON card
FOR EACH ROW BEGIN
– 更新数据
UPDATE fed_card SET
card_agent_id=new.card_agent_id,
card_merchant_id=new.card_merchant_id,
card_assigntime=new.card_assigntime,
card_member_id=new.card_member_id,
card_usetime=new.card_usetime,
card_pay=new.card_pay,
card_freeze=new.card_freeze,
card_usetype=new.card_usetype
WHERE card_no=old.card_no; – 由于数据的迁移导致不同的库中表id并不一定唯一

END;

$$

DELIMITER ;

– 卡删除触发器

DELIMITER $$

CREATE
TRIGGER card_delete_trigger AFTER DELETE ON card
FOR EACH ROW BEGIN
– 更新数据
DELETE FROM fed_card WHERE card_no=old.card_no; – 由于数据的迁移导致不同的库中表id并不一定唯一

END;

$$

DELIMITER ;

– 用户插入触发

DELIMITER $$

CREATE

TRIGGER `member_insert_trigger` AFTER INSERT ON `member` 
FOR EACH ROW BEGIN
-- 插入数据（若被触发表中存在唯一的索引insert 触发会失败）
INSERT INTO `fed_member`(

member_id,
member_uniqueid,
member_username,
member_realname,
member_password,
member_mobile,
member_gender,
member_headportrait,
member_email,
member_address,
member_regarea,
member_regtime,
member_regip
) VALUES (
new.member_id,
new.member_uniqueid,
new.member_username,
new.member_realname,
new.member_password,
new.member_mobile,
new.member_gender,
new.member_headportrait,
new.member_email,
new.member_address,
new.member_regarea,
new.member_regtime,
new.member_regip
);
END;
$$

DELIMITER ;

– 用户更新触发

DELIMITER $$

CREATE
TRIGGER member_update_trigger AFTER UPDATE ON member
FOR EACH ROW BEGIN
– 更新数据（就算被监视的表sql update没有更新字段也会按照触发的sql update被触发到表中）
UPDATE fed_member SET
member_username=new.member_username,
member_realname=new.member_realname,
member_password=new.member_password,
member_mobile=new.member_mobile,
member_gender=new.member_gender,
member_headportrait=new.member_headportrait,
member_email=new.member_email,
member_address=new.member_address,
member_chargecard=new.member_chargecard,
member_surpluscard=new.member_surpluscard,
member_chargecardnum=new.member_chargecardnum,
member_chargecash=new.member_chargecash,
member_surpluscash=new.member_surpluscash,
member_getcashgift=new.member_getcashgift,
member_surpluscashgift=new.member_surpluscashgift,
member_exchangenum=new.member_exchangenum,
member_status=new.member_status
WHERE member_id=old.member_id;
END;
$$

DELIMITER ;

– 用户消费记录插入触发器

DELIMITER $$

CREATE
TRIGGER memberrecord_insert_trigger AFTER INSERT ON memberrecord
FOR EACH ROW BEGIN
– 插入数据（若被触发表中存在唯一的索引insert 触发会失败）
INSERT INTO fed_memberrecord(
record_member_id,
record_membertype_id,
record_merchant_id,
memberrecord_merchant_id,
record_createtime,
record_money,
record_desc,
record_istype
) VALUES (
new.record_member_id,
new.record_membertype_id,
new.record_merchant_id,
new.memberrecord_merchant_id,
new.record_createtime,
new.record_money,
new.record_desc,
new.record_istype
);
END;
$$

DELIMITER

– 消费记录不可更新，只能插入

– 订单触发器插入

DELIMITER $$

CREATE
TRIGGER productorder_insert_trigger AFTER INSERT ON productorder
FOR EACH ROW BEGIN
– 插入数据（若被触发表中存在唯一的索引insert 触发会失败，存在这样的情况）
INSERT INTO fed_productorder(
– productorder_id,
productorder_sn,
productorder_member_id,
productorder_agent_id,
productorder_user_name,
productorder_merchant_id,
productorder_status ,
productorder_shipping_status,
productorder_pay_status,
productorder_addressname,
productorder_consignee,
productorder_addressid,
productorder_country,
productorder_province,
productorder_city,
productorder_district,
productorder_address,
productorder_zipcode,
productorder_mobile,
productorder_email ,
productorder_postscript,
productorder_shipping_id,
productorder_shipping_name,
productorder_pay_id,
productorder_pay_name,
productorder_amount,
productorder_card,
productorder_cashgift,
productorder_shipping_fee,
productorder_order_amount,
productorder_money_paid,
productorder_add_time,
productorder_pay_time,
productorder_giving_cashgift,
productorder_subfreight,
productorder_mobile_activity

) VALUES (
– new.productorder_id,
new.productorder_sn,
new.productorder_member_id,
new.productorder_agent_id,
new.productorder_user_name,
new.productorder_merchant_id,
new.productorder_status ,
new.productorder_shipping_status,
new.productorder_pay_status,
new.productorder_addressname,
new.productorder_consignee,
new.productorder_addressid,
new.productorder_country,
new.productorder_province,
new.productorder_city,
new.productorder_district,
new.productorder_address,
new.productorder_zipcode,
new.productorder_mobile,
new.productorder_email ,
new.productorder_postscript,
new.productorder_shipping_id,
new.productorder_shipping_name,
new.productorder_pay_id,
new.productorder_pay_name,
new.productorder_amount,
new.productorder_card,
new.productorder_cashgift,
new.productorder_shipping_fee,
new.productorder_order_amount,
new.productorder_money_paid,
new.productorder_add_time,
new.productorder_pay_time,
new.productorder_giving_cashgift,
new.productorder_subfreight,
new.productorder_mobile_activity
);
END;
$$

DELIMITER ;

– 更新订单信息触发器

DELIMITER $$

CREATE
TRIGGER productorder_update_trigger AFTER UPDATE ON productorder
FOR EACH ROW BEGIN
– 更新订单
UPDATE fed_productorder SET
productorder_status=new.productorder_status ,
productorder_shipping_status=new.productorder_shipping_status,
productorder_pay_status=new.productorder_pay_status,
productorder_addressname=new.productorder_addressname,
productorder_consignee=new.productorder_addressname,
productorder_addressid=new.productorder_addressid,
productorder_country=new.productorder_country,
productorder_province=new.productorder_province,
productorder_city=new.productorder_city,
productorder_district=new.productorder_district,
productorder_address=new.productorder_address,
productorder_zipcode=new.productorder_zipcode,
productorder_mobile=new.productorder_mobile,
productorder_postscript=new.productorder_postscript,
productorder_pay_id=new.productorder_pay_id,
productorder_pay_name =new.productorder_pay_name,
productorder_amount=new.productorder_amount,
productorder_card=new.productorder_card,
productorder_cashgift=new.productorder_cashgift,
productorder_shipping_fee=new.productorder_shipping_fee,
productorder_order_amount =new.productorder_order_amount,
productorder_money_paid =new.productorder_money_paid,
productorder_confirm_time=new.productorder_confirm_time,
productorder_pay_time=new.productorder_pay_time,
productorder_sendtime=new.productorder_sendtime,
productorder_receipttime=new.productorder_receipttime,
productorder_logisticscompany=new.productorder_logisticscompany,
productorder_logisticsnumber=new.productorder_logisticsnumber ,
productorder_logisticsinfo=new.productorder_logisticsinfo,
productorder_qtlogisticsinfo=new.productorder_qtlogisticsinfo,
productorder_giving_cashgift=new.productorder_giving_cashgift ,
productorder_mobile_activity =new.productorder_mobile_activity
WHERE productorder_sn=old.productorder_sn;
END;
$$

DELIMITER ;

– 订单下产品插入触发器

DELIMITER $$

CREATE

TRIGGER `productorderproduct_insert_trigger` AFTER INSERT ON `productorderproduct` 
FOR EACH ROW BEGIN
-- 插入数据（若被触发表中存在唯一的索引insert 触发会失败）
INSERT INTO `fed_productorderproduct`(

productorderproduct_productorder_sn,
productorderproduct_member_id ,
productorderproduct_merchant_id,
productorderproduct_agent_id,
productorderproduct_product_id,
productorderproduct_product_type_id,
productorderproduct_product_name,
productorderproduct_product_thumpimage ,
productorderproduct_product_number ,
productorderproduct_exchange_price ,
productorderproduct_exchange_card ,
productorderproduct_givecashgift,
productorderproduct_shipping_fee,
productorderproduct_shippingtypevalue,
productorderproduct_time,
productorderproduct_endtime,
productorderproduct_subfreight,
productorderproduct_cashgift ,
productorderproduct_product_tel,
productorderproduct_mobile,
productorderproduct_mobile_code,
productorderproduct_expire_time,
productorderproduct_status
) VALUES (
new.productorderproduct_productorder_sn,
new.productorderproduct_member_id ,
new.productorderproduct_merchant_id,
new.productorderproduct_agent_id,
new.productorderproduct_product_id,
new.productorderproduct_product_type_id,
new.productorderproduct_product_name,
new.productorderproduct_product_thumpimage ,
new.productorderproduct_product_number ,
new.productorderproduct_exchange_price ,
new.productorderproduct_exchange_card ,
new.productorderproduct_givecashgift,
new.productorderproduct_shipping_fee,
new.productorderproduct_shippingtypevalue,
new.productorderproduct_time,
new.productorderproduct_endtime,
new.productorderproduct_subfreight,
new.productorderproduct_cashgift ,
new.productorderproduct_product_tel,
new.productorderproduct_mobile,
new.productorderproduct_mobile_code,
new.productorderproduct_expire_time,
new.productorderproduct_status
);
END;
$$

DELIMITER ;