Oracle中Trigger例子1

发表于:2007-06-07来源:作者:点击数: 标签:
er Oracle 中Triggle例子1 CREATE OR REPLACE TRIGGER QMAILB.T_MSG_SI_TRIGGER AFTER INSERT ON QMAILB.T_MSG_MO REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE tmpVar NUMBER; v_link_id t_msg_mt.LINK_ID%type; v_src_termid t_msg_mt.SRC_TER
er

Oracle中Triggle例子1

CREATE OR REPLACE TRIGGER QMAILB.T_MSG_SI_TRIGGER
AFTER INSERT
ON QMAILB.T_MSG_MO
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
tmpVar NUMBER;
v_link_id t_msg_mt.LINK_ID%type;
v_src_termid t_msg_mt.SRC_TERMID%type;
v_dst_termid t_msg_mt.DST_TERMID%type;
v_dst_termtype t_msg_mt.DST_TERMTYPE%type;
v_fee_termid t_msg_mt.FEE_TERMID%type;
v_fee_termtype t_msg_mt.FEE_TERMTYPE%type;
v_fee_type t_msg_mt.FEE_TYPE%type;
v_fee_code t_msg_mt.FEE_CODE%type;
v_fee_usertype t_msg_mt.FEE_USERTYPE%type;
v_service_code t_msg_mt.SERVICE_CODE%type;
v_mt_type t_msg_mt.MT_TYPE%type;
v_udhi t_msg_mt.UDHI%type;
v_pid t_msg_mt.PID%type;
v_schedule_time t_msg_mt.SCHEDULE_TIME%type;
v_expire_time t_msg_mt.EXPIRE_TIME%type;
v_pk_total t_msg_mt.PK_TOTAL%type;
v_pk_num t_msg_mt.PK_NUM%type;
v_report_flag t_msg_mt.REPORT_FLAG%type;
v_msg_level t_msg_mt.MSG_LEVEL%type;
v_msg_format t_msg_mt.MSG_FORMAT%type;
v_msg t_msg_mt.MSG%type;
v_msg_uid t_msg_mt.MSG_UID%type;


v_url mobile_type_list.link_url%type;
v_explain mobile_type_list.explain_info%type;
v_phone_type  mobile_type_list.mobile_type%type;
v_mo_msg t_msg_mo.MSG%type;
v_version mobile_type_list.VERSION%type;
v_ip vendor.VENDOR_IP%type;
v_mail_addr push_user.EMAIL_ADDR%type;
v_pop_port cef.CMPOP_PORT%type;
v_smtp_port cef.CMSMTP_PORT%type;
v_dst_port cef.CMSMTP_PORT%type;
v_src_port cef.CMSMTP_PORT%type;
--v_ca mobile_type_list.mobile_type%type;
strLength number;


/******************************************************************************
   NAME:      
   PURPOSE:   

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        2005-4-6             1. Created this trigger.

   NOTES:

   Automatically available Auto Replace Keywords:
      Object Name:    
      Sysdate:         2005-4-6
      Date and Time:   2005-4-6, 13:53:23, and 2005-4-6 13:53:23
      Username:         (set in TOAD Options, Proc Templates)
      Table Name:       (set in the "New PL/SQL Object" dialog)
      Trigger Options:  (set in the "New PL/SQL Object" dialog)
******************************************************************************/
BEGIN

 v_link_id:= null;
 v_src_termid:= :new.dst_termid;
 v_dst_termid:= :new.src_termid;
 v_dst_termtype:=0;
 v_fee_termid:=null;
 v_fee_termtype :=0;
 v_fee_type:='01';
 v_fee_code:='00';
 v_fee_usertype:=0;
 v_mt_type :=0;
 v_pid:=0;
 v_schedule_time:=null;
 v_expire_time:=null;
 v_pk_total:=0;
 v_pk_num:=0;
 v_report_flag:=0;
 v_msg_level:=2;
 v_msg_uid:=null;


    v_mo_msg := upper(:new.msg); 
 
 --***************************************************
 --*********************   send si  ******************
 --***************************************************

 if substr(v_mo_msg,0,2) = 'SI' then
       strLength:=length(substr(v_mo_msg,3));
    --not have aclearcase/" target="_blank" >ccount in pushmail, only send the soft link bases on the mobile type and number;
    if strLength>0 then
    v_phone_type:=substr(v_mo_msg,3) ;
    select mobile_type_list.LINK_URL,mobile_type_list.EXPLAIN_INFO
    into v_url,v_explain
    from mobile_type_list
    where mobile_type = v_phone_type; 

    --have account in pushmail yet,
    else
    
    select mobile_type_list.LINK_URL,mobile_type_list.EXPLAIN_INFO,push_user.DEV_ID
    into v_url,v_explain,v_phone_type
    from mobile_type_list,push_user
    where push_user.PUSH_USER_ID=:new.src_termid and push_user.DEV_ID=mobile_type_list.MOBILE_TYPE;
    end if;

  
   v_msg:= system.SENDSMS.makeSiData(v_url,v_explain,v_phone_type);
  
   if substr(v_phone_type,0,3) = 'DPD' then
     v_udhi := 0;
     v_msg_format :=15;
   else
     v_udhi :=1;
        v_msg_format :=4;  
   end if;
   
     --insert the record to t_msg_mt table
      insert into T_MSG_MT(LINK_ID,SRC_TERMID,DST_TERMID,DST_TERMTYPE,FEE_TERMID,FEE_TERMTYPE,SERVICE_CODE,FEE_TYPE,FEE_CODE,FEE_USERTYPE,MT_TYPE,UDHI,PID,SCHEDULE_TIME,EXPIRE_TIME,PK_TOTAL,PK_NUM,REPORT_FLAG,MSG_LEVEL,MSG_FORMAT,MSG,msg_uid)
   values(v_LINK_ID,v_SRC_TERMID,v_DST_TERMID,v_DST_TERMTYPE,v_FEE_TERMID,v_FEE_TERMTYPE,v_SERVICE_CODE,v_FEE_TYPE,v_FEE_CODE,v_FEE_USERTYPE,v_MT_TYPE,v_UDHI,v_PID,v_SCHEDULE_TIME,v_EXPIRE_TIME,v_PK_TOTAL,v_PK_NUM,v_REPORT_FLAG,v_MSG_LEVEL,v_MSG_FORMAT,v_MSG,v_msg_uid);
   
 end if;
 
 
   --***************************************************
   --**************  send ca  ****************8
   --***************************************************
    if v_mo_msg = 'CA' then

      select mobile_type_list.LINK_URL,mobile_type_list.EXPLAIN_INFO
   into v_url,v_explain
   from mobile_type_list
   where mobile_type = 'CA';
   v_phone_type:='CA';
 
   v_msg:= system.SENDSMS.makeCaData(v_url,v_explain,v_phone_type);

   v_udhi :=1;
      v_msg_format :=4;  
 
 
 
      --insert the record to t_msg_mt table
       insert into T_MSG_MT(LINK_ID,SRC_TERMID,DST_TERMID,DST_TERMTYPE,FEE_TERMID,FEE_TERMTYPE,SERVICE_CODE,FEE_TYPE,FEE_CODE,FEE_USERTYPE,MT_TYPE,UDHI,PID,SCHEDULE_TIME,EXPIRE_TIME,PK_TOTAL,PK_NUM,REPORT_FLAG,MSG_LEVEL,MSG_FORMAT,MSG,msg_uid)
    values(v_LINK_ID,v_SRC_TERMID,v_DST_TERMID,v_DST_TERMTYPE,v_FEE_TERMID,v_FEE_TERMTYPE,v_SERVICE_CODE,v_FEE_TYPE,v_FEE_CODE,v_FEE_USERTYPE,v_MT_TYPE,v_UDHI,v_PID,v_SCHEDULE_TIME,v_EXPIRE_TIME,v_PK_TOTAL,v_PK_NUM,v_REPORT_FLAG,v_MSG_LEVEL,v_MSG_FORMAT,v_MSG,v_msg_uid);
   
 
   end if;
  
  
   if v_mo_msg = 'CADPD' then

      select mobile_type_list.LINK_URL,mobile_type_list.EXPLAIN_INFO
   into v_url,v_explain
   from mobile_type_list
   where mobile_type = 'CA';
   v_phone_type:='DPD';
 
   v_msg:= system.SENDSMS.makeCaData(v_url,v_explain,v_phone_type);
 

   v_udhi := 0;
   v_msg_format :=15;

      --insert the record to t_msg_mt table
      insert into T_MSG_MT(LINK_ID,SRC_TERMID,DST_TERMID,DST_TERMTYPE,FEE_TERMID,FEE_TERMTYPE,SERVICE_CODE,FEE_TYPE,FEE_CODE,FEE_USERTYPE,MT_TYPE,UDHI,PID,SCHEDULE_TIME,EXPIRE_TIME,PK_TOTAL,PK_NUM,REPORT_FLAG,MSG_LEVEL,MSG_FORMAT,MSG,msg_uid)
   values(v_LINK_ID,v_SRC_TERMID,v_DST_TERMID,v_DST_TERMTYPE,v_FEE_TERMID,v_FEE_TERMTYPE,v_SERVICE_CODE,v_FEE_TYPE,v_FEE_CODE,v_FEE_USERTYPE,v_MT_TYPE,v_UDHI,v_PID,v_SCHEDULE_TIME,v_EXPIRE_TIME,v_PK_TOTAL,v_PK_NUM,v_REPORT_FLAG,v_MSG_LEVEL,v_MSG_FORMAT,v_MSG,v_msg_uid);
   
 
   end if;
  
  
   --***************************************************
   --*******************  send setting  ***************
   --***************************************************
   if substr(v_mo_msg,0,2) = 'ST' then

             tmpVar := 0;
     --set msg parameters from tables bases on the mobile number(src_termid)
            select
                mobile_type_list.LINK_URL,mobile_type_list.EXPLAIN_INFO,mobile_type_list.MOBILE_TYPE,cef.SMS_NUMBER,mobile_type_list.VERSION,vendor.vendor_ip,push_user.email_addr,cef.cmpop_port, cef.cmsmtp_port
       into
              v_url,v_explain,v_phone_type,v_service_code,v_version,v_ip,v_mail_addr,v_pop_port,v_smtp_port
       from
              push_user, mobile_type_list,cef,vendor
    where
          push_user.PUSH_USER_ID=:new.src_termid and mobile_type_list.MOBILE_TYPE=push_user.DEV_ID and push_user.CID=cef.CID and vendor.vendor_id=cef.vendor_id;
   
      
       if substr(upper(v_phone_type),0,3) = 'DPD' then
       v_dst_port:='2948';
    else
     v_dst_port:='16000';
    end if;
    v_src_port:='9200';
    v_version:='2.0';
    v_msg:=system.SENDSMS.makeSettingData(v_dst_port,v_src_port,v_version,'Y','N','9999',v_ip,v_smtp_port,v_ip,v_pop_port,v_mail_addr);
    DBMS_output.put_line(v_msg);
    v_udhi :=1;
       v_msg_format :=4;
 
      --insert the record to t_msg_mt table
       insert into T_MSG_MT(LINK_ID,SRC_TERMID,DST_TERMID,DST_TERMTYPE,FEE_TERMID,FEE_TERMTYPE,SERVICE_CODE,FEE_TYPE,FEE_CODE,FEE_USERTYPE,MT_TYPE,UDHI,PID,SCHEDULE_TIME,EXPIRE_TIME,PK_TOTAL,PK_NUM,REPORT_FLAG,MSG_LEVEL,MSG_FORMAT,MSG,msg_uid)
    values(v_LINK_ID,v_SRC_TERMID,v_DST_TERMID,v_DST_TERMTYPE,v_FEE_TERMID,v_FEE_TERMTYPE,v_SERVICE_CODE,v_FEE_TYPE,v_FEE_CODE,v_FEE_USERTYPE,v_MT_TYPE,v_UDHI,v_PID,v_SCHEDULE_TIME,v_EXPIRE_TIME,v_PK_TOTAL,v_PK_NUM,v_REPORT_FLAG,v_MSG_LEVEL,v_MSG_FORMAT,v_MSG,v_msg_uid);
      
   end if;
  
  
  
    EXCEPTION
      WHEN OTHERS THEN
        -- Consider logging the error and then re-raise
        RAISE;
END ;
/


原文转自:http://www.ltesting.net