Oracle中Trigger例子1

王朝mssql·作者佚名  2006-12-17
窄屏简体版  字體: 小|中|大|超大  

Oracle中Trigger例子1

Oracle中Trigger例子1 erOracle中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 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 ;

/

 
 
 
免责声明：本文为网络用户发布，其观点仅代表作者个人观点，与本站无关，本站仅提供信息存储服务。文中陈述内容未经本站证实，其真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航