mysql 触发器示例和注解

-- 格式
CREATE TRIGGER   触发器名称
AFTER|before     insert|update|delete  ON 触发表
FOR EACH ROW
BEGIN
        insert into  处理表values(..,...,...)
        update  处理表 set id=new.id,name=new.name,...
    delete from t_zg_loanee_apply_trigger where id=old.ID;
END;

-- 注释
after  :执行后触发
before: 执行前触发
new   :代表触发表的插入后或者更新后的数据
old     :代表触发表更新前的数据信息或删除之前的数据信息

 

-- insert
drop trigger if exists t_afterinsert_on_tab1;

CREATE TRIGGER t_afterinsert_on_tab1
AFTER INSERT ON t_zg_loanee_apply
FOR EACH ROW
BEGIN
     insert into 
t_zg_loanee_apply_trigger(ID,LOAN_DATE,PRODUCT_ID,OOP_ORG_NAME,REPAY_TYPE,DELETE_STATUS,CREATEDON,DEBT_TYPE,OBTAIN_CHANNEL,PACKAGE_NUMBER,LOCK_STATUS,IS_TPAY)
 values(new.ID,
new.LOAN_DATE,
new.PRODUCT_ID,
new.OOP_ORG_NAME,
new.REPAY_TYPE,
new.DELETE_STATUS,
new.CREATEDON,DEBT_TYPE,
new.OBTAIN_CHANNEL,
new.PACKAGE_NUMBER,
new.LOCK_STATUS,
new.IS_TPAY);
END;

-- update 
drop trigger if exists t_afterinsert_on_t_zg_loanee_apply_upd;

CREATE TRIGGER t_afterinsert_on_t_zg_loanee_apply_upd
AFTER UPDATE  ON t_zg_loanee_apply
FOR EACH ROW
BEGIN
    UPDATE   t_zg_loanee_apply_trigger  SET
LOAN_DATE=new.LOAN_DATE,
PRODUCT_ID=new.PRODUCT_ID,
OOP_ORG_NAME=new.OOP_ORG_NAME,
REPAY_TYPE=new.REPAY_TYPE,
DELETE_STATUS=new.DELETE_STATUS,
CREATEDON=new.CREATEDON,
DEBT_TYPE=new.DEBT_TYPE,
OBTAIN_CHANNEL=new.OBTAIN_CHANNEL,
PACKAGE_NUMBER=new.PACKAGE_NUMBER,
LOCK_STATUS=new.LOCK_STATUS,
IS_TPAY=new.IS_TPAY
 where id=old.ID;
END;

-- delete 
drop trigger if exists t_afterinsert_on_t_zg_loanee_apply_del;

CREATE TRIGGER t_afterinsert_on_t_zg_loanee_apply_del 
AFTER delete  ON t_zg_loanee_apply
FOR EACH ROW
BEGIN
    delete from t_zg_loanee_apply_trigger where id=old.ID;
END;