Sometimes we want to send an email when something suspicious happens in a database. Oracle is able to natively send emails.
Example: We want to receive an alert every time a user is deleted, so that we can immediately check if this happened for a good reason or not.
database and perform some checks about the most important data. However this would be overcomplicated, and requires the installation of some tools.
Here we’ll see how to achieve the same result with a trigger.
CREATE TRIGGER audit_user_delete BEFORE DELETE ON user FOR EACH ROW DECLARE v_user_id INT; v_mail UTL_SMTP.connection; BEGIN v_mail := UTL_SMTP.open_connection('smtp.example.com', 25); UTL_SMTP.helo(l_mail_conn, 'smtp.example.com'); UTL_SMTP.mail(l_mail_conn, 'email@example.com'); UTL_SMTP.rcpt(l_mail_conn, 'firstname.lastname@example.org'); UTL_SMTP.data(l_mail_conn, 'User has been deleted: ' || :old.id); UTL_SMTP.quit(l_mail_conn); END; /
Here’s what happens when a user is deleted:
audit_user_deletetrigger is fired;
UTL_SMTPinstance is assigned to
v_mail, which sends an email;
:old.idis the deleted user’s id.