miércoles, 3 de junio de 2015

TRIGGER ENVIA CORREO

sqlplus / as sysdba
 
@?/rdbms/admin/utlsmtp.sql
@?/rdbms/admin/prvtsmtp.plb
@?/rdbms/admin/utlmail.sql
@?/rdbms/admin/prvtmail.plb
GRANT EXECUTE ON UTL_MAIL TO PUBLIC;
 
ALTER SESSION SET CURRENT SCHEMA=myschema;
 
 
CREATE OR REPLACE TRIGGER myschema.mytrigger
AFTER DELETE ON myschema.mytable 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 => 'mysender@company.com',
                 recipients => 'myrecipient1@company.com',
                 cc => 'myrecipient2@company.com',
                        subject => 'Myapplication: A row has been deleted in the table mytable',
                        message => 'A row has been deleted in the table mytable.'       ||  utl_tcp.CRLF ||  utl_tcp.CRLF ||
                '   Customer informations: ' ||  utl_tcp.CRLF ||
                '       Name: ' || :old.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;
/