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;