Oracle Trigger Procedure Letter

Recently wrote a Web service to many different stations of the client call, and directly send a letter to notify the program of the abnormal.

Set the trigger (after insert) of a table directly in Oracle, and send it to the manager of the site when there is a new, abnormal information to write into.

/*Written by Milo-on 2017-03-09*-touch transmitter sends email*/CREATE OR REPLACE TRIGGERSmall_program_exception_afin AfterINSERT  onsmall_program_exception forEach ROWDECLAREl_htmlVARCHAR2(32767); V_station_namevarchar( -); V_email_stringvarchar2( +); V_program_namevarchar2( -); V_station_codevarchar( -);BEGIN  /*dbms_output.put_line (' new.station: ' | |: new.station);  Dbms_output.put_line (' new.program_id: ' | |: new.program_id);                       Dbms_output.put_line (' new.occurred_time: ' | |  To_char (: New.occurred_time, ' Yyyy-mm-dd hh24:mm:ss '));                       Dbms_output.put_line (' new.program_id: ' | |  SUBSTR (: New.exception_message, 1, 32767)); */  BEGIN    --Access Program name, recipient email, and station name    SelectS.stn_name, M.mail_for_error, P.name,s.stn_code intoV_station_name, v_email_string, V_program_name,v_station_code fromtk_cust_prog_m m, Tk_cust_prog_d D, station S, Tk_programe pwhereM.custom_no=D.custom_no andM.custom_no=S.stn_code andd.prog_id=p.id andd.prog_id=: new.program_id andD.custom_no=: new.station; Exception whenNo_data_found ThenRaise_application_error (-20001,'No data found.'); --dbms_output.put_line (' No_data_found ');      --dbms_output.put_line (' v_email_string: ' | | v_email_string);      if(v_email_string is NULL orV_email_string= ' ') ThenRaise_application_error (-20001,                                'Errors Email recipient should not being empty, please type the recipient"'s email address on the web-function'); End if; END; L_html:= '' ||V_station_code|| '</p><p> This e-mail is to notify unexpected error occurred in the program. Please check it asap.</p><p> Exception message</p><p>-------------------------------------- ----------------------------------------</p><p> <b>' ||V_station_name|| '</b></p><p> <b>' ||V_program_name|| '</b></p><p> Posted on' ||To_char (: New.occurred_time,'mm-dd-yyyy Hh24:mm:ss')|| '</p><p>' ||SUBSTR (: New.exception_message,1,32767)|| '</p><p>------------------------------------------------------------------------------</p ></body>';Send_mail (p_to=v_email_string, P_from= '[Email protected]***.com',--* * * @oecgroup. comP_subject= 'Program occurred Errors Notice', P_text_msg= '', P_html_msg=l_html, P_smtp_host= 'spam.***.com', P_account= '[Email protected]***.com', P_password= '***');END;/

Oracle Trigger Procedure Letter