-- ############################################################
-- # Creator: Vincent Fenoll
-- # Created: 2011/04/19
-- # Name: Email Sys_context trigger 
-- ############################################################
-- #
-- # Compatible: Oracle 10g 11g
-- #
-- ############################################################
-- #
-- # Send an email in a trigger (here after the deletion of a 
-- # row in a table)and display informations about 
-- # user sys context (e.g who deleted this row)
-- # 
-- ############################################################

-- Pe-requisites
Connected like SYS: 
@?/rdbms/admin/utlsmtp.sql 
@?/rdbms/admin/prvtsmtp.plb 
@?/rdbms/admin/utlmail.sql 
@?/rdbms/admin/prvtmail.plb 
GRANT EXECUTE ON UTL_MAIL TO PUBLIC;


CREATE OR REPLACE TRIGGER TRG_emp 
AFTER DELETE ON emp REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW 
begin 
        EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''127.0.0.1'''; 
          UTL_MAIL.send(sender => 'vincent@oraclescripts.net', 
                 recipients => 'cyrille@oraclescripts.net', 
                        subject => 'Deletion of an employee', 
                        message => 'An employee has been deleted'      ||  utl_tcp.CRLF ||  utl_tcp.CRLF || 
                '   Informations: ' ||  utl_tcp.CRLF || 
                '       Employee name: ' || :old.EMP_NAME  ||  utl_tcp.CRLF || 
                '       Country  : ' || :old.COUNTRY    ||  utl_tcp.CRLF || utl_tcp.CRLF || 
                '   Technical Informations: ' ||  utl_tcp.CRLF || 
                '       DB Name: ' || SYS_CONTEXT('USERENV','DB_NAME')   ||  utl_tcp.CRLF || 
                '       OS USER: ' || SYS_CONTEXT('USERENV','OS_USER')      ||  utl_tcp.CRLF || 
                '       Session ID: ' || SYS_CONTEXT('USERENV','SESSIONID')     ||  utl_tcp.CRLF || 
                '       Session User: ' || SYS_CONTEXT('USERENV','SESSION_USER')    ||  utl_tcp.CRLF || 
                '       IP Address: ' ||  SYS_CONTEXT('USERENV','IP_ADDRESS')    ||  utl_tcp.CRLF || 
                '       Terminal: ' || SYS_CONTEXT('USERENV','TERMINAL')      ||  utl_tcp.CRLF || 
                '       Is DBA: ' || SYS_CONTEXT('USERENV','ISDBA') , 
                      mime_type => 'text; charset=us-ascii'); 
end;