触发器

create or replace trigger reportTrigger
after insert on urm.srm_t_runtime_session
for each row

declare
v_eqamIp varchar2(16);
v_assetId varchar2(50);
v_clientId  varchar2(40);
v_ipqamName varchar2(100);
v_assetName varchar2(128);
v_flag number;
begin

v_assetId := hextostring(:new.asset_id);
v_clientId := clientidtostbsn(:new.client_id);


  begin
    select qam.video_ip1 into v_eqamIp from urm.srm_t_ipqam qam where qam.ipqam_id = (
           select p.ipqam_id from urm.srm_t_qam_port p where p.qam_port_id=(
                  select c.qam_port_id from urm.srm_t_vod_channel c where c.qam_channel_id=:new.qam_channel_id
           )
    );
   exception
   when others then
   v_eqamIp:='';

  end;

  begin
    select i.ipqam_name into v_ipqamName from srm_t_ipqam i where ipqam_id= (
           select p.ipqam_id from srm_t_qam_port p where p.qam_port_id=(
           select c.qam_port_id from srm_t_vod_channel c where c.qam_channel_id=:new.qam_channel_id
           )
    );
    exception
     when others then
     v_ipqamName:='';
   end;

   begin
     select INSTR(v_assetId,'RTI',1) into v_flag from dual;
     if (v_flag>0) then
      select a.assetdescription into v_assetName from bms.asset a where a.assetid=v_assetId;
     else
      select r.assetname into v_assetName from mc.report_purchase_log r where r.assetid=v_assetId and rownum<2 order by r.id desc ;
     end if;

     exception
     when others then
      select a.assetname into v_assetName from bms.asset a where a.assetid=v_assetId;
   end;



  insert into mc.report_play_log(session_id, start_time, ssp_server_id, op_type_name, user_id, stb_ip_add ,stb_ip_port ,rtsp_agent_id ,qam_channel_id, asset_id ,release_request
  , update_time, client_id, bitrate, eqamip, id,ipqam_Name,assetname) 
  values (:new.session_id, :new.start_time, :new.ssp_server_id, :new.op_type_name, :new.user_id, :new.stb_ip_add ,:new.stb_ip_port ,:new.rtsp_agent_id ,
  :new.qam_channel_id, v_assetid ,:new.release_request, :new.update_time, v_clientId, :new.bitrate, v_eqamIp ,SEQ_REPORT_PLAY_LOG.NEXTVAL,v_ipqamName,v_assetName) ;

end;