Postgresql 創建觸發器，刪除觸發器和 禁用觸發器

 CREATE  OR REPLACE FUNCTION  XF_VIP_AFUPD_WX()
RETURNS trigger AS $$
DECLARE
  i_count integer;
  s_wx_openid varchar(256);
  s_docno varchar(60);
  s_wx_code varchar(256);
  s_wx_cardid varchar(256);
begin
if (old.xf_currentbonus!=new.xf_currentbonus) then
    select count(*) into i_count from wx_userinfo where vipaccountno = new.xf_vipaccountno;

    if (i_count > 0) then
      select wx_openid, wx_code, wx_cardid into s_wx_openid, s_wx_code, s_wx_cardid
             from wx_userinfo where vipaccountno = new.xf_vipaccountno and rownum = 1;

      s_docno = 'WBS' || new.xf_vipcode || to_char(current_timestamp, 'yymmddhh24miss') || ABS(MOD(DBMS_RANDOM.RANDOM,1000));

      insert into wx_vip_bonus_sync(docno, mall_id, wx_openid, wx_code, wx_cardid, vipcode, vipaccountno, grade, issue_date, expiry_date, bf_bonus, cr_bonus, createtime, status)
             values(s_docno, new.xf_issuestore, s_wx_openid, s_wx_code, s_wx_cardid, new.xf_vipcode, new.xf_vipaccountno, new.xf_grade, new.xf_issuedate, new.xf_expirydate, nvl(old.xf_currentbonus, 0), nvl(new.xf_currentbonus, 0), sysdate, '10');
    end if;

  end if;
end;
$$
LANGUAGE plpgsql;

 

CREATE TRIGGER "A_U_CRM_MSGSEND"  AFTER UPDATE --BEFORE UPDATE
 ON 

"public"."CRM_MSGSEND" FOR EACH ROW EXECUTE PROCEDURE

 "public"."a_u_crm_msgsend" ();

 

ALTER TABLE "public"."CRM_MSGSEND" 
DISABLE TRIGGER "A_D_CRM_MSGSEND";

 

DROP TRIGGER "XF_VIPACTIVITLYSIGNUP_INS"  ON "public"."XF_VIPACTIVITLYSIGNUP"