Home » SQL & PL/SQL » SQL & PL/SQL » Problem in craeting trigger
Problem in craeting trigger [message #208310] Sat, 09 December 2006 05:40 Go to next message
Ankur Bhatia
Messages: 14
Registered: May 2006
Location: India
Junior Member

Hello Friends,

I have create a trigger to send mail automatically when any Order is approved. Here is the code:

create or replace trigger kpm_test_mail_trg
after insert or update on po_action_history
for each row
when(new.action_code='APPROVE' and new.object_type_code like 'PO')
declare
FromAddress Varchar2(500) := 'ankur.bhatia@khannapaper.com';
ToAddress2 varchar2(500) := 'ankur.bhatia@khannapaper.com';
EmailServer varchar2(30) := '192.168.0.221';
/*** LOCAL SMTP PORT NUMBER ***/
Port number := 25;
/*** CONNECTION STRING ***/
conn UTL_SMTP.CONNECTION;
/*** LINE FEED **/
crlf VARCHAR2(2):= CHR(13) || CHR(10);
/*** MESSAGE BODY **/
mesg VARCHAR2(5000);
mesg_body varchar2(5000);
text varchar2(5000);
/*** SUBJECT OF THE MAIL ***/
Subj varchar2(100);
BEGIN
insert into test_req_mail(supplier) values(:new.ACTION_CODE);
/*** ESTABLISHING A CONNECTION OF EMAIL SERVER WITH LOCAL PORT ***/
conn:= utl_smtp.open_connection( EmailServer, Port );
/*** SETTING VARIOUS PARAMETRS FOR THE MAIL ***/
utl_smtp.helo( conn, EmailServer);
utl_smtp.mail( conn, FromAddress);
utl_smtp.rcpt( conn, ToAddress2);
Subj := 'Test Mail';
text := 'Test Mail';
mesg:=
'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )|| crlf ||
'From:'||FromAddress|| crlf ||
'Subject: '|| Subj || crlf ||
'To: '||ToAddress2|| crlf ||
''|| crlf || text;
utl_smtp.data( conn, mesg );
utl_smtp.quit( conn );
END;


The problem is that if i remove the condition new.action_code='APPROVE' the it works properly and send mail for any action which happens. But if I apply the condition as shown above it overwrites the original value of the ACTION_CODE with the NULL value.

I am not understanding, actually what is happening.
Please provide me the solution ASAP!!!

Thanks in advance,
Ankur
Re: Problem in craeting trigger [message #208325 is a reply to message #208310] Sat, 09 December 2006 10:02 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Ankur Bhatia wrote on Sat, 09 December 2006 12:40
But if I apply the condition as shown above it overwrites the original value of the ACTION_CODE with the NULL value.

I find that hard to believe, since you don't assign a value to that column.
Show us an exact copy-paste from sqlplus where you show an update followed by a select of the updated row.
Previous Topic: SYS.XMLTYPE AND OUTPUT
Next Topic: URGENT--Memory size of binary Increasing due 2multithreading
Goto Forum:
  


Current Time: Tue Dec 06 00:29:24 CST 2016

Total time taken to generate the page: 0.10009 seconds