I have made stored procedure and executing through trigger.So my requirements is like that if we create a new issue first statement should execute and if we return the same issue number then 2 nd statement should execute.
I have make like this but in if statement is giving wrong.
NNAME VARCHAR2(50);
subj VARCHAR2(200);
bdy varchar2(2000);
mailto VARCHAR2(200);
err_level number;
BEGIN
SELECT NICKNAME INTO NNAME FROM AXUSERS WHERE USERNAME=:NEW.USERNAME;
select email INTO mailto from axusers where username=:new.assign_tousr;
if (:new.issue_no) then
PRO_EMAILFORMAT ('New Issue Creation','<ISSNO>= '||:new.issue_no || ',' || '<ISSUEDESC>= '||:new.issue_desc || ' , '||'<USERNICKNAME>= '|| nname || ' , ' || '<ISSSTATUS>= '||:NEW.ISSUE_STATUS || ' , ' ||'<ASSIGNTO>= '||:NEW.ASSIGN_TO,'E',mailto, NULL);
else
PRO_EMAILFORMAT ('Issue Return','<ISSNO>= '||:old.issue_no || ',' || '<NICKNAME>= '|| nname || ' , '||'<ASSIGNTO>= '||:NEW.ASSIGN_TO || ' , ' || '<RETURNREMARKS>= '||:new.return_remarks,'E',mailto, NULL);
end if;
end;