mysql 触发器示例和注解

时间:2021-10-29 05:06:42
 
 
-- 格式
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;