Oracle DB:有关电子邮件触发器的建议 [英] Oracle DB: Suggestion for Email Trigger

问题描述

如果表中的特定列已更新并更新同一表中的其他列，有人可以提供链接或Oracle DB触发器示例来发送电子邮件吗?

Can someone provide a link or an example of an Oracle DB trigger that sends an email if a specific column in the table gets updated AND updates a different column in the same table?

示例: 一个表有多个问题，并且有两列已添加问题"和已发送电子邮件"，默认为"0"

Example: A table has multiple issues and there are two columns 'Issue Added' and 'Email Sent' that default to '0'

Issue Added    Email Sent
     0             0

已添加问题"列更新为"1"时

When the 'Issue Added' column gets updated to '1'

Issue Added    Email Sent
     1             0

触发器发送电子邮件并更新已发送电子邮件"列

Trigger sends email and updates column 'Email Sent'

Issue Added    Email Sent
     1             1

推荐答案

尝试在触发器中发送电子邮件通常是个坏主意.

It would generally be a bad idea to try to send an email in a trigger.

1. 如果系统无法发送电子邮件(例如，由于SMTP服务器暂时关闭)，则触发器将失败，并且触发语句将失败并回滚.很少因为您无法发送电子邮件而真正想要停止基础交易的情况很少.
2. 发送电子邮件是非事务性的.这意味着您将为永不更改的更改发送电子邮件.而且您将多次发送电子邮件，因为Oracle选择回滚并重新执行INSERT语句的全部或部分以保持写入一致性.

1. If the system is unable to send the email (for example, because the SMTP server is temporarily down), the trigger will fail and the triggering statement will fail and be rolled back. It is very rare that you would really want to stop the underlying transaction simply because you weren't able to send an email.
2. Sending an email is non-transactional. That means that you'll send emails for changes that never get committed. And you'll send emails multiple times because Oracle chooses to rollback and re-execute all or part of an INSERT statement in order to maintain write consistency.

通常，通过数据库作业可以更好地为您服务，该数据库作业定期查找需要发送电子邮件的行，发送电子邮件，然后更新表.您可以使用旧的DBMS_JOB软件包，也可以使用更新的和更复杂的DBMS_SCHEDULER软件包.类似于

You'll generally be much better served with a database job that periodically looks for rows that need to have an email sent, sends the emails, and then updates the table. You can use either the older DBMS_JOB package or the newer and more sophisticated DBMS_SCHEDULER package. Something along the lines of

CREATE OR REPLACE PROCEDURE process_issues
AS
BEGIN
  FOR i IN (SELECT * 
              FROM your_table_name
             WHERE issue_added = 1
               AND email_sent  = 0)
  LOOP
    send_email( i.issue_id );
    UPDATE your_table_name
       SET email_sent = 1
     WHERE issue_id   = i.issue_id;
  END LOOP;
END;

然后计划每5分钟运行一次(您也可以使用DBMS_SCHEDULER软件包)

which is then scheduled to run, say, every 5 minutes (you could also use the DBMS_SCHEDULER package)

DECLARE
  l_jobno PLS_INTEGER:
BEGIN
  dbms_job.submit( l_jobno,
                   'BEGIN process_issues; END;',
                   sysdate + interval '5' minute,
                   'sysdate + interval ''5'' minute' );
  commit;
END;

您可以使用 UTL_MAIL程序包实现send_email过程.您可能只需要使用适当的参数来调用UTL_MAIL.SEND(假设您已经配置了SMTP_OUT_SERVER参数，并且已经为您的用户授予了对UTL_MAIL程序包和ACL的适当访问权限，该ACL允许您与该SMTP服务器进行通信).

You can use the UTL_MAIL package to implement the send_email procedure. You probably just need to call UTL_MAIL.SEND with appropriate parameters (assuming you've configured your SMTP_OUT_SERVER parameter and your user has been granted appropriate access to the UTL_MAIL package and to an ACL that allows you to communicate with that SMTP server).

这篇关于Oracle DB:有关电子邮件触发器的建议的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！