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 包和允许您与该 SMTP 服务器通信的 ACL).

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屋！