Some one plz help me (MAHENDRA D RATHOD) - Mailing list pgsql-admin

<div align="left"><font face="Arial"><span style="font-size:10pt">My self is Mahendra D Rathod.</span></font></div><div
align="left"><br/></div><div align="left"><font face="Arial"><span style="font-size:10pt">I have some problem in
executingtrigger at backend side</span></font></div><div align="left"><br /></div><div align="left"><font
face="Arial"><spanstyle="font-size:10pt">One trigger is fired AFTER INSERT OR UPDATE ON
 mtrl_issue_detail</span></font></div><divalign="left"><br /></div><div align="left"><font face="Arial"><span
style="font-size:10pt">Sowhen any row of "mtrl_issue_detail" is inserted or updated this trigger is fired on one
function.</span></font></div><divalign="left"><br /></div><div align="left"><font face="Arial"><span
style="font-size:10pt">Thisfunction updates the same row that has just been inserted or
updated.</span></font></div><divalign="left"><br /></div><div align="left"><font face="Arial"><span
style="font-size:10pt">Nowwhen this process happens the database gets HANGED. and i have to restart the database on
server.</span></font></div><divalign="left"><br /></div><div align="left"><font face="Arial"><span
style="font-size:10pt">Isthere any solution to do this process without affecting my server.</span></font></div><div
align="left"><fontface="Arial"><span
style="font-size:10pt">********************************************************************************</span></font></div><div
align="left"><fontface="Arial"><span style="font-size:10pt">FUNCTION: taxcal_fun()</span></font></div><div
align="left"><br/></div><div align="left"><font face="Arial"><span
style="font-size:10pt">********************************************************************************</span></font></div><div
align="left"><br/></div><div align="left"><font face="Arial"><span style="font-size:10pt">CREATE FUNCTION taxcal_fun()
returnsopaque as'</span></font></div><div align="left"><font face="Arial"><span
style="font-size:10pt">DECLARE</span></font></div><divalign="left"><font face="Arial"><span style="font-size:10pt">
 recRECORD;</span></font></div><div align="left"><font face="Arial"><span style="font-size:10pt">  tmp_rate
REAL;</span></font></div><divalign="left"><font face="Arial"><span style="font-size:10pt">  bl_amt
REAL;</span></font></div><divalign="left"><font face="Arial"><span style="font-size:10pt">  ex_amt
REAL;</span></font></div><divalign="left"><font face="Arial"><span style="font-size:10pt">  ad_amt
REAL;</span></font></div><divalign="left"><font face="Arial"><span style="font-size:10pt">  les_amt
REAL;</span></font></div><divalign="left"><font face="Arial"><span style="font-size:10pt">  t_amt
REAL;</span></font></div><divalign="left"><font face="Arial"><span style="font-size:10pt">  rel_per
REAL;</span></font></div><divalign="left"><font face="Arial"><span style="font-size:10pt">  rel_frac
REAL;</span></font></div><divalign="left"><font face="Arial"><span style="font-size:10pt">  tx_amt
REAL;</span></font></div><divalign="left"><font face="Arial"><span style="font-size:10pt">  ad_tax
REAL;</span></font></div><divalign="left"><font face="Arial"><span style="font-size:10pt">  gr_amt
REAL;</span></font></div><divalign="left"><br /></div><div align="left"><font face="Arial"><span
style="font-size:10pt">BEGIN</span></font></div><divalign="left"><br /></div><div align="left"><font face="Arial"><span
style="font-size:10pt"> SELECT INTO rec * FROM rate_mst r, mtrl_issue_mst m </span></font></div><div align="left"><font
face="Arial"><spanstyle="font-size:10pt">      WHERE r.m_code = NEW.mtrl_code</span></font></div><div
align="left"><fontface="Arial"><span style="font-size:10pt">      AND m.issue_no = NEW.issue_no</span></font></div><div
align="left"><fontface="Arial"><span style="font-size:10pt">      AND r.effective_dt = (SELECT MAX(effective_dt) FROM
rate_mst</span></font></div><divalign="left"><font face="Arial"><span style="font-size:10pt">
                             WHERErate_mst.m_code=NEW.mtrl_code </span></font></div><div align="left"><font
face="Arial"><spanstyle="font-size:10pt">                              AND rate_mst.effective_dt <=
m.issue_dt);</span></font></div><divalign="left"><br /></div><div align="left"><font face="Arial"><span
style="font-size:10pt"> IF rec.issue_type = ''stk_trf_sale'' THEN</span></font></div><div align="left"><font
face="Arial"><spanstyle="font-size:10pt">     tmp_rate := rec.trf_rate;</span></font></div><div align="left"><font
face="Arial"><spanstyle="font-size:10pt">  ELSIF rec.issue_type = ''con_sale'' THEN</span></font></div><div
align="left"><fontface="Arial"><span style="font-size:10pt">        tmp_rate := rec.con_rate;</span></font></div><div
align="left"><fontface="Arial"><span style="font-size:10pt">  ELSIF rec.issue_type = ''gen_sale''
THEN</span></font></div><divalign="left"><font face="Arial"><span style="font-size:10pt">             tmp_rate :=
rec.gen_rate_cs;</span></font></div><divalign="left"><font face="Arial"><span style="font-size:10pt">  ELSIF
rec.issue_type= ''agent_sale'' THEN</span></font></div><div align="left"><font face="Arial"><span
style="font-size:10pt">        tmp_rate := rec.soc_rate_cs;</span></font></div><div align="left"><font
face="Arial"><spanstyle="font-size:10pt">  ELSE rec.issue_type = ''mrp_sale'' THEN</span></font></div><div
align="left"><fontface="Arial"><span style="font-size:10pt">         tmp_rate :=
rec.mrp_rate_cs;</span></font></div><divalign="left"><font face="Arial"><span style="font-size:10pt">  END
IF;</span></font></div><divalign="left"><br /></div><div align="left"><font face="Arial"><span style="font-size:10pt">
 IFrec.tax_code = ''c'' OR rec.tax_code = ''C'' THEN</span></font></div><div align="left"><font face="Arial"><span
style="font-size:10pt">            tmp_rate:= tmp_rate + CAST(round(tmp_rate * rec.cst_per / 100,2) AS
REAL);</span></font></div><divalign="left"><font face="Arial"><span style="font-size:10pt">            rel_per  :=
rec.cst_per;</span></font></div><divalign="left"><font face="Arial"><span style="font-size:10pt">            rel_frac
:=rec.cst_frac;</span></font></div><div align="left"><font face="Arial"><span style="font-size:10pt">  ELSIF
rec.tax_code= ''g'' or rec.tax_code = ''G'' THEN</span></font></div><div align="left"><font face="Arial"><span
style="font-size:10pt">            tmp_rate:= tmp_rate + CAST(round(tmp_rate * rec.gst_per / 100,2) AS
REAL);</span></font></div><divalign="left"><font face="Arial"><span style="font-size:10pt">            rel_per  :=
rec.gst_per;</span></font></div><divalign="left"><font face="Arial"><span style="font-size:10pt">            rel_frac
:=rec.gst_frac;</span></font></div><div align="left"><font face="Arial"><span style="font-size:10pt">  ELSE
</span></font></div><divalign="left"><font face="Arial"><span style="font-size:10pt">            tmp_rate :=
tmp_rate;</span></font></div><divalign="left"><font face="Arial"><span style="font-size:10pt">            rel_per  :=
0;</span></font></div><divalign="left"><font face="Arial"><span style="font-size:10pt">            rel_frac :=
0;</span></font></div><divalign="left"><font face="Arial"><span style="font-size:10pt">  END
IF;</span></font></div><divalign="left"><br /></div><div align="left"><font face="Arial"><span style="font-size:10pt">
 bl_amt:= CAST((NEW.issueqty_cs * tmp_rate) AS REAL);</span></font></div><div align="left"><font face="Arial"><span
style="font-size:10pt"> ex_amt:= bl_amt*(rec.exise_per/100)*(rec.exise_frac/100);</span></font></div><div
align="left"><fontface="Arial"><span style="font-size:10pt">  ad_amt  := NEW.issueqty_cs *
rec.add_other_cs;</span></font></div><divalign="left"><font face="Arial"><span style="font-size:10pt">  les_amt :=
cast((NEW.issueqty_mt* 1000 * rec.less_other_kg) AS REAL);</span></font></div><div align="left"><font
face="Arial"><spanstyle="font-size:10pt">  t_amt  := CAST((bl_amt + ex_amt + ad_amt - les_amt) AS
REAL);</span></font></div><divalign="left"><font face="Arial"><span style="font-size:10pt">  </span></font></div><div
align="left"><fontface="Arial"><span style="font-size:10pt">  tx_amt := t_amt * (rel_per/100) *
(rel_frac/100);</span></font></div><divalign="left"><font face="Arial"><span style="font-size:10pt">  ad_tax := tx_amt
*(rec.additional_tax/100) * (rec.additional_frac/100); </span></font></div><div align="left"><font face="Arial"><span
style="font-size:10pt"> gr_amt := t_amt + tx_amt + ad_tax;</span></font></div><div align="left"><br /></div><div
align="left"><fontface="Arial"><span style="font-size:10pt">   </span></font><font color="#0000ff" face="Arial"><span
style="font-size:10pt">UPDATEmtrl_issue_detail </span></font></div><div align="left"><font color="#0000ff"
face="Arial"><spanstyle="font-size:10pt">      SET rate_per_cs=tmp_rate,</span></font></div><div align="left"><font
color="#0000ff"face="Arial"><span style="font-size:10pt">          bill_amt=bl_amt,</span></font></div><div
align="left"><fontcolor="#0000ff" face="Arial"><span style="font-size:10pt">
         exise_amt=ex_amt,</span></font></div><divalign="left"><font color="#0000ff" face="Arial"><span
style="font-size:10pt">         add_amt=ad_amt,</span></font></div><div align="left"><font color="#0000ff"
face="Arial"><spanstyle="font-size:10pt">          less_amt=les_amt,</span></font></div><div align="left"><font
color="#0000ff"face="Arial"><span style="font-size:10pt">          tot_amt=t_amt,</span></font></div><div
align="left"><fontcolor="#0000ff" face="Arial"><span style="font-size:10pt">
         tax_amt=tx_amt,</span></font></div><divalign="left"><font color="#0000ff" face="Arial"><span
style="font-size:10pt">         add_tax=ad_tax,</span></font></div><div align="left"><font color="#0000ff"
face="Arial"><spanstyle="font-size:10pt">          gross_amt=gr_amt</span></font></div><div align="left"><font
color="#0000ff"face="Arial"><span style="font-size:10pt">    WHERE issue_no=NEW.issue_no</span></font></div><div
align="left"><fontcolor="#0000ff" face="Arial"><span style="font-size:10pt">      AND
mtrl_code=NEW.mtrl_code</span></font></div><divalign="left"><font color="#0000ff" face="Arial"><span
style="font-size:10pt">     AND batch_code=NEW.batch_code;</span></font></div><div align="left"><br /></div><div
align="left"><fontface="Arial"><span style="font-size:10pt">RETURN NULL;</span></font></div><div align="left"><br
/></div><divalign="left"><font face="Arial"><span style="font-size:10pt">END;'</span></font></div><div align="left"><br
/></div><divalign="left"><font face="Arial"><span style="font-size:10pt">LANGUAGE 'plpgsql';</span></font></div><div
align="left"><br/></div><div align="left"><font face="Arial"><span
style="font-size:10pt">********************************************************************************</span></font></div><div
align="left"><fontface="Arial"><span style="font-size:10pt">TRIGGER : taxcal_trg</span></font></div><div
align="left"><fontface="Arial"><span
style="font-size:10pt">********************************************************************************</span></font></div><div
align="left"><fontface="Arial"><span style="font-size:10pt">CREATE TRIGGER taxcal_trg </span></font></div><div
align="left"><fontface="Arial"><span style="font-size:10pt">   AFTER INSERT OR UPDATE ON
mtrl_issue_detail</span></font></div><divalign="left"><font face="Arial"><span style="font-size:10pt">      FOR EACH
ROWEXECUTE PROCEDURE taxcal_fun();</span></font></div><div align="left"><br /></div><div align="left"><br /></div><div
align="left"><fontface="Arial"><span style="font-size:10pt">Whe the </span></font><font color="#0000ff"
face="Arial"><spanstyle="font-size:10pt">BLUE </span></font><font color="#000000" face="Arial"><span
style="font-size:10pt">partis executed the problem arises</span></font></div><div align="left"><font color="#000000"
face="Arial"><spanstyle="font-size:10pt">and without this part is runs smoothly.</span></font></div><div
align="left"><br/></div><div align="left"><font color="#000000" face="Arial"><span style="font-size:10pt">Is there any
solutionupdate this </span></font><font color="#0000ff" face="Arial"><span
style="font-size:10pt">query</span></font><fontcolor="#000000" face="Arial"><span
style="font-size:10pt">.</span></font></div><divalign="left"></div>