MySQL 捕获存储过程

# drop table _capture_etpbase_margin_record


# 需要修改的信息，3项：
#原始自增列（本例中为id） 表名（本例为etpbase_margin_record） 字段列表


# 创建暂存表
CREATE TABLE _capture_etpbase_margin_record AS 
SELECT * FROM `etpbase_margin_record` ;  #limit 0 




# 添加主键列
ALTER TABLE  _capture_etpbase_margin_record  ADD _record_id INT AUTO_INCREMENT PRIMARY KEY FIRST;


#添加标识列
ALTER TABLE  _capture_etpbase_margin_record  ADD _changeSign VARCHAR(20); #1 增加 2 修改  3删除




#添加标识列
ALTER TABLE  _capture_etpbase_margin_record  ADD _add_time DATETIME; #1 增加 2 修改  3删除


#创建索引


CREATE INDEX idx__capture_etpbase_margin_record_id ON _capture_etpbase_margin_record(id);




#创建触发器
DELIMITER $$


CREATE TRIGGER tr_etpbase_margin_record_delete AFTER DELETE
    ON etpbase_margin_record
    FOR EACH ROW BEGIN
UPDATE _capture_etpbase_margin_record
SET _changeSign='delete',_add_time=CURRENT_TIMESTAMP()
WHERE id=old.id;
    END$$
DELIMITER ;


DELIMITER $$
CREATE TRIGGER tr_etpbase_margin_record_update AFTER UPDATE
    ON etpbase_margin_record
    FOR EACH ROW BEGIN
INSERT INTO _capture_etpbase_margin_record
(
    `id`,
    `bo_id`,
    `operator_bo_id`,
    `user_type`,
    `oper_type`,
    `oper_status`,
    `re_oper_status`,
    `biz_id_type`,
    `biz_id`,
    `goods_id`,
    `order_id`,
    `occ_money`,
    `rule_id`,
    `rule_condition`,
    `real_formula`,
    `relevant_id`,
    `ulck_ufz_id`,
    `s_time`,
    `e_time`,
    `is_virtual`,
    `fin_platform_id`,
    `pr_id`,
    `system_id`,
    `data_remark`,
    `create_time`,
    `version_remark`,
    `vno`,
    `is_enabled`,
    `_changeSign`,
    `_add_time`
    )
    VALUES
    (
    NEW.`id`,
    NEW.`bo_id`,
    NEW.`operator_bo_id`,
    NEW.`user_type`,
    NEW.`oper_type`,
    NEW.`oper_status`,
    NEW.`re_oper_status`,
    NEW.`biz_id_type`,
    NEW.`biz_id`,
    NEW.`goods_id`,
    NEW.`order_id`,
    NEW.`occ_money`,
    NEW.`rule_id`,
    NEW.`rule_condition`,
    NEW.`real_formula`,
    NEW.`relevant_id`,
    NEW.`ulck_ufz_id`,
    NEW.`s_time`,
    NEW.`e_time`,
    NEW.`is_virtual`,
    NEW.`fin_platform_id`,
    NEW.`pr_id`,
    NEW.`system_id`,
    NEW.`data_remark`,
    NEW.`create_time`,
    NEW.`version_remark`,
    NEW.`vno`,
    NEW.`is_enabled`,
    'update' ,CURRENT_TIMESTAMP()
    );
    END$$    
DELIMITER ;


DELIMITER $$  
CREATE TRIGGER tr_etpbase_margin_record_insert AFTER INSERT
    ON etpbase_margin_record
    FOR EACH ROW BEGIN
INSERT _capture_etpbase_margin_record
(
    `id`,
    `bo_id`,
    `operator_bo_id`,
    `user_type`,
    `oper_type`,
    `oper_status`,
    `re_oper_status`,
    `biz_id_type`,
    `biz_id`,
    `goods_id`,
    `order_id`,
    `occ_money`,
    `rule_id`,
    `rule_condition`,
    `real_formula`,
    `relevant_id`,
    `ulck_ufz_id`,
    `s_time`,
    `e_time`,
    `is_virtual`,
    `fin_platform_id`,
    `pr_id`,
    `system_id`,
    `data_remark`,
    `create_time`,
    `version_remark`,
    `vno`,
    `is_enabled`,
    `_changeSign`,
    `_add_time`
    )
    VALUES
    (
    NEW.`id`,
    NEW.`bo_id`,
    NEW.`operator_bo_id`,
    NEW.`user_type`,
    NEW.`oper_type`,
    NEW.`oper_status`,
    NEW.`re_oper_status`,
    NEW.`biz_id_type`,
    NEW.`biz_id`,
    NEW.`goods_id`,
    NEW.`order_id`,
    NEW.`occ_money`,
    NEW.`rule_id`,
    NEW.`rule_condition`,
    NEW.`real_formula`,
    NEW.`relevant_id`,
    NEW.`ulck_ufz_id`,
    NEW.`s_time`,
    NEW.`e_time`,
    NEW.`is_virtual`,
    NEW.`fin_platform_id`,
    NEW.`pr_id`,
    NEW.`system_id`,
    NEW.`data_remark`,
    NEW.`create_time`,
    NEW.`version_remark`,
    NEW.`vno`,
    NEW.`is_enabled`,
    'insert' ,CURRENT_TIMESTAMP()
    );
    END$$
DELIMITER ;