Thread: Some one plz help me (MAHENDRA D RATHOD)

Some one plz help me (MAHENDRA D RATHOD)

From
"Ramesh PAtel "
Date:
<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> 

Re: Some one plz help me (MAHENDRA D RATHOD)

From
Oliver Elphick
Date:
On Mon, 2003-05-12 at 11:53, Ramesh PAtel wrote:
> My self is Mahendra D Rathod.
>
> I have some problem in executing trigger at backend side
>
> One trigger is fired AFTER INSERT OR UPDATE ON  mtrl_issue_detail
>
> So when any row of "mtrl_issue_detail" is inserted or updated this
> trigger is fired on one function.
>
> This function updates the same row that has just been inserted or
> updated.
>
> Now when this process happens the database gets HANGED. and i have to
> restart the database on server.

This is hardly surprising, because you have explicitly set up an
infinite loop.

If you wish to change the record being updated or inserted, you should
use a BEFORE trigger, change NEW (the record to be inserted or the
after-update record) and return NEW.

Check the documentation:
  Programmer's Manual
    Procedural Languages
      PL/pgSQL
        Trigger procedures

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Study to show thyself approved unto God, a workman
      who does not need to be ashamed and who correctly
      handles the word of truth."          II Timothy 2:15