Programming Oracle Triggers and Stored Procedures (3rd Edition) (Prentice Hall PTR Oracle Series)

Database triggers can interface with database pipes and alerts. Figure 11-16 illustrates a model in which a database trigger posts a notification that a professor 's salary has changed. This is a trigger that fires only from an update of the row. The trigger posts the alert. When the transaction commits, the signal is received by a second Oracle database connection. This connection is represented in Figure 11-16 as PROCESS_ALERTS, which has one purpose, to deliver email.

Because PROCESS_ALERTS runs asynchronously, it has no impact on other database activity. Update transactions to the PROFESSORS table do not wait for an email to be sent.

We start with developing an interface that will service email requests . This interface will be used by PROCESS_ALERTS. It will accept standard email parameters: sender, receiver, subject, and text. This interface is a package and has the following specification:

CREATE OR REPLACE PACKAGE email_pkg IS PROCEDURE send (p_sender IN VARCHAR2, p_recipient IN VARCHAR2, p_message IN VARCHAR2, p_subject IN VARCHAR2); END email_pkg;

The next step is to develop an engine that will dedicate itself to servicing signals. This can be a stand-alone procedure. Figure 11-16 shows PROCESS_ALERTS as a stand-alone procedure. At this point we need to consider using a single procedure, and a package could be a better choice.

We need a procedure to receive alerts and a procedure to send alerts. The sending occurs in the trigger. It seems reasonable to define a package specification to support the send and receive functions. That package specification is shown next.

CREATE OR REPLACE PACKAGE alerts_pkg IS PROCEDURE process_alerts; PROCEDURE send_alert(message IN VARCHAR2); END alerts_pkg;

Figure 11-16 is redrawn to show the modified architecture. In Figure 11-17, the trigger calls the SEND_ALERT procedure to post the alert. The code used to receive the alert is in the same package.

The assumption is that the trigger will use the professor's name to construct an email address and pass that address to the procedure in the ALERTS_PKG package. Ideally, the PROFESSORS table would have a column that contains email addresses.

The database trigger is set up to send an email only when there is a difference in the old and new salary.

CREATE OR REPLACE TRIGGER professors_aur AFTER UPDATE ON professors FOR EACH ROW WHEN (OLD.SALARY <> NEW.SALARY) BEGIN alerts_pkg.send_alert(:new.prof_name'@domain.com'); END;

For this model, all interfaces have been shown. We can start looking at the body for the individual packages. The email body is shown here. This body includes global declarations for the SMTP server IP address and port number. This is the mechanism by which the package ALERTS_PKG will deliver email for each alert received. The body of ALERTS_PKG will include a call to the email SEND procedure.

CREATE OR REPLACE PACKAGE BODY email_pkg IS g_smtp_server CONSTANT VARCHAR2(20) := '00.00.00.00'; g_smtp_server_port CONSTANT PLS_INTEGER := 25; PROCEDURE send (p_sender IN VARCHAR2, p_recipient IN VARCHAR2, p_message IN VARCHAR2, p_subject IN VARCHAR2) IS mail_conn utl_smtp.connection; BEGIN mail_conn := utl_smtp.open_connection (g_smtp_server, g_smtp_server_port); utl_smtp.helo (mail_conn, g_smtp_server); utl_smtp.mail (mail_conn, p_sender); utl_smtp.rcpt (mail_conn, p_recipient); utl_smtp.open_data(mail_conn); utl_smtp.write_data (mail_conn,'From: "'p_sender '" <'p_sender'>'utl_tcp.CRLF); utl_smtp.write_data (mail_conn,'To: "'p_recipient '" <'p_recipient'>'utl_tcp.CRLF); utl_smtp.write_data (mail_conn, 'Subject: ' p_subjectutl_tcp.CRLF); utl_smtp.write_data (mail_conn, utl_tcp.CRLFp_message); utl_smtp.close_data(mail_conn); utl_smtp.quit (mail_conn); END send; END email_pkg;

The package body for the sending and receiving of alerts is shown next. The subprogram to receive alerts is coded to wait for three alerts; each wait includes a 10-sec timer. The loop also terminates when it receives an alert message of "END." The alert device name is "email_notification."

For an anachronous application, a separate process that runs in the background will invoke PROCESS_ALERTS. Locally, PROCESS_ALERTS can be run from SQL*Plus. As coded here, it will deliver the first three emails that result from updates to the PROFESSORS table.

CREATE OR REPLACE PACKAGE BODY alerts_pkg IS PROCEDURE process_alerts IS professor_email VARCHAR2(100); status INTEGER; BEGIN dbms_alert.register('email_notification'); FOR I IN 1..3 LOOP dbms_alert.waitone (name => 'email_notification', message => professor_email, status => status, timeout => 10); IF status = 0 THEN EXIT WHEN professor_email = 'END'; email_pkg.send (p_sender=>'admin@school.com', p_recipient=>professor_email, p_subject=>'Salary', p_message=>'Salary has changed'); END IF; END LOOP; END process_alerts; PROCEDURE send_alert(message IN VARCHAR2) IS BEGIN dbms_alert.signal('email_notification', message); END send_alert; END alerts_pkg;