Oracle Trigger Example 1

Oracle ER
Oracle Triggle Example 1

CREATE OR REPLACE TRIGGER qmailb. T_msg_si_triggerafter Inserton qmailb. T_msg_mo referencing new as new 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 of 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 inserts 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 inserts 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)           & nbsp 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;/