How can I use a trigger export HTML and send mail

ximen · ‎2013 Oct 10

Questions: when the table into A table (trigger) when the sum (a.ar_amt) > B.limi to send A fine warning sign that E-mail to me. The current problems are: the trigger does not support using the "output to html" , I can't export subsidiary If the export after so I can put the file name as a variable, because have a different trans_no. Cannot use triggers, stored procedures, the export file send mail, please give me some advice: thank you

 A table: TRANS_No    part_id  quantity   unit_price     ar_amt       customer_id
         TR001       A001    10                30          300      Customer001

         TR001       A004    20                 40          800      customer001
  B tabel:

          customer_id     limit

          customer001       900

Declare tot integer;    
DECLARE @result  LONG VARCHAR;  
Declare lim integer;  
    Select sum(amt) into tot where customer_id = newrow.customer_id;     
    Select limit into lim where customer_id = newrow.customer_id;
If tot > lim then
    ---If so start send result to   mail
                  CALL xp_startsmtp( smtp_sender = 'mfkpie8@163.com', smtp_server='smtp.163.com', smtp_port='25',
timeout=240, smtp_auth_username='name', smtp_auth_password='password');
CALL xp_sendmail( recipient='mfkpie8@163.com',
      subject='ssssssss',  content_type = 'text/html',
     include_file = 'c:\\\\outputtableA_trans_no.html' 
);
call xp_stopmail();

endif

updata new question( 2013-11-30):

en:Production of HTML format for this statement (bease is wrong result with my code) ：

<html><head><title>test</title><meta http-equiv="content-type" content="text/html;charset=GBK"/></head><body>trans_nopart_idquantityunit_pricear_amtcustomer_id<table_x0020_border>TR001TR001204080customer001TR001A004204080000customer001</table_x0020_border></body></html>

Q：

Q1: en:I am now trying to increase "< table border > < / table >" encountered problems, Spaces are escaped (the red into space) please see pictures) beacase: Spaces convert to "x0020"

Q2. chang </table_x0020_border> to The correct HTML for the below :

I want the result is: <html><head><title>测试</title><meta http-equiv="content-type" content="text/html;charset=GBK"/></head><body>trans_nopart_idquantityunit_pricear_amtcustomer_id

TR001	TR001	20	40	80	customer001
TR001	A004	20	40	80000	customer001

</body></html>

jeff_albion · ‎2013 Nov 27

You are correct - OUTPUT TO cannot be used directly in a trigger (as it is only available in Interactive SQL). The closest thing you can do is use the UNLOAD SQL statement from the server-side, but this does not support HTML formatting - only raw data exports.

You will need to create your own HTML result set from the results you're interested in and then e-mail the result. It wasn't clear from your question whether you actually need to write out the HTML file first to a temporary file on the file system first, or if you're only doing this in order to reference it in the include_file - not writing out the file out to the file system and generating the HTML in-memory will be faster:

Example data:

create table a_table (
   trans_no varchar(64),
   part_id varchar(64),
   quantity int,
   unit_price int,
   ar_amt int,
   customer_id varchar(128)
 );

insert into a_table values ('TR001', 'A001', 10, 30, 300, 'customer001');

create table b_table (
   customer_id varchar(128),
   "limit" int
);

insert into b_table values ('customer001', 900);
commit;

Now that we have the data, we can build up a trigger to check the amounts and send the appropriate HTML message out directly in the SMTP e-mail message:

create or replace trigger check_max_limit
before insert, update
on a_table
referencing new as new_row
for each row
begin
  declare @curr_limit int;
  declare @tot_limit int;
  declare @html_result long varchar;

  -- get current limit results for the incoming row customer_id 
  select sum(ar_amt), "b_table"."limit"
    into @curr_limit, @tot_limit
    from a_table, b_table
   where a_table.customer_id = new_row.customer_id
         and a_table.customer_id = b_table.customer_id
   group by "limit";

  -- check the limit
  if (@curr_limit + new_row.ar_amt > @tot_limit ) then

    -- generate HTML using "xmlelement" from results
    select xmlelement( name "html",
           (xmlelement( name "body",
            (xmlelement( name "table",
              xmlelement( name "thead", 
                xmlelement( name "tr", 
                  xmlelement( name "th", 'trans_no' ),
                  xmlelement( name "th", 'part_id' ),
                  xmlelement( name "th", 'quantity' ),
                  xmlelement( name "th", 'unit_price' ),
                  xmlelement( name "th", 'ar_amt' ),
                  xmlelement( name "th", 'customer_id' )
                )
             ),
             xmlelement( name "tbody",
                xmlagg( xmlelement( name "tr", 
                          xmlelement( name "td", trans_no ),
                          xmlelement( name "td", part_id ),
                          xmlelement( name "td", quantity ),
                          xmlelement( name "td", unit_price ),
                          xmlelement( name "td", ar_amt ),
                          xmlelement( name "td", customer_id )
                      ) ) ) ) ) ) )
    ) into @html_result
      from a_table
     where customer_id = new_row.customer_id;

    -- send e-mail with HTML content
    call xp_startsmtp( 'doe@sample.com', 'corporatemail.sample.com' );
    call xp_sendmail( recipient='jane.smith@sample.com',
                       subject='This is my subject line',
                      "message"=@html_result ); 
    call xp_stopsmtp( );

    -- (Optional) rollback current operation that triggered limit check
    rollback trigger; 
  end if;
end;

Finally to trigger the limit check and send an e-mail:

insert into a_table values ('TR001', 'A004', 20, 40, 800, 'customer001');

The trigger fires any time there is a record inserted or updated on a_table and ensures that the summed ar_amt always is lower than the b_table."limit" value. I used the XMLELEMENT function to generate the HTML text.

MarkCulp · ‎2013 Oct 10

I'm going to take a random guess that you are wanting to send email from the database server.

Did you look at the examples in the documentation for xp_sendmail? You should be able to cut-and-paste and then modify as needed any of the four examples to get a working solution.

Example: Here is the first example in the v16 documention:

CALL xp_startsmtp( 'doe@sample.com', 'corporatemail.sample.com' );
CALL xp_sendmail( recipient='jane.smith@sample.com',
                  subject='This is my subject line',
                  "message"='This text is the body of my email.\\n' ); 
CALL xp_stopsmtp( );

Former Member · ‎2013 Nov 22

Oh, Just saw this question on this forum. I think the author of this message want to ask the following question. The parameter "query"="select * from

" in the store procedure xp_sendmail() can't really send the result of the query to the recipient.

I just did the same test in my local environment of ASA12.0.1. It really can't work.

Here is the test script: create table t(id int primary key, col2 varchar(32)); insert into t values(1 ,'wang'); commit;

CALL xp_startsmtp( smtp_sender = '<userid>@163.com', smtp_server='smtp.163.com', smtp_port='25', timeout=240, smtp_auth_username='<userid>', smtp_auth_password='<password>');

CALL xp_sendmail( recipient='iihero@qq.com',subject='test_result',"message"='Oh, I hope there is query result as well',query='select * from t');

call xp_stopmail();

After that, the received email only contains the message content of "Oh, ....", but there isn't anything about the query result.

The ASA document doesn't give more details about how to ensure the query result be sent to the target recipient.

Could anybody help answer this question if this is an user error? Otherwise, this should be a bug of ASA. Thanks.

Name	Character	Unicode code point (decimal)	Standard	Description
quot	"	U+0022 (34)	XML 1.0	double quotation mark
amp	&	U+0026 (38)	XML 1.0	ampersand
apos	'	U+0027 (39)	XML 1.0	apostrophe (apostrophe-quote)
lt	<	U+003C (60)	XML 1.0	less-than sign
gt	>	U+003E (62)	XML 1.0	greater-than sign

How can I use a trigger export HTML and send mail

Know the answer?

Need more details?

Accepted Solutions (0)

Answers (3)

Answers (3)