两表比较后利用触发器发送邮件——ASA数据库

ASA数据库利用触发器发送HTML邮件.感谢：iHERO,Jeff Albion,jason.hinsperger给予帮助！问题是：

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

--sum(a.ar_amt)>b.limit --Are not allowed to insert data
--sum(a.ar_amt)<b.limit --can insertdata
当A表某客户的数据播入的数据大于B表的金额额度时发送提醒业务：

测试数据：

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;


--ASA 数据库不支持在过程与触发器中使用out 与in 交互式函数所以我们以xmlelement拼接出HTML格式的邮件--
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 "head",   
                xmlelement(name "title", 't表转换'),  
                xmlelement(name "META", xmlattributes('Content-Type' as "http-equiv" , 'text/html;charset=GBK' as "content"))  
            ),  
            (xmlelement (name "body",    
             xmlelement(name "table", xmlattributes('1' as "border"),
                xmlelement(name "thead", 
                    xmlelement(name "tr",  
                    xmlelement(name "th",  'trans_no'),  
                    xmlelement(name "th", 'part_id')  
                )  
                ),  
                xmlelement(name "tbody",  
                    xmlagg( xmlelement (name "tr",  
                       xmlelement(name "td", trans_no),  
                       xmlelement(name "td", part_id)  
                    ))))))) into @html_result from a_table  where customer_id = new_row.customer_id;


    -- send e-mail with HTML content
    call xp_startsmtp(  smtp_sender = 'mfkpie8@163.com', smtp_server='smtp.163.com', smtp_port='25',
timeout=240, smtp_auth_username='mfkpie8', smtp_auth_password='PASSWORD' );
    call xp_sendmail( recipient=@mailid,
                       subject='This is my subject line',
                      "message"=@html_result,content_type = 'text/html'); 
    call xp_stopsmtp( );


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