er
oracle中triggle例子1
create or replace trigger qmailb.t_msg_si_triggerafter inserton qmailb.t_msg_mo referencing new as new old as oldfor each rowdeclaretmpvar 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 account 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 ;/
