-- ############################################################
-- # 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;
Articles similaires