<body>
<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 executing trigger
at backend side</span></font></div>
<div align="left"><br>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">One trigger is fired AFTER INSERT
OR UPDATE ON mtrl_issue_detail</span></font></div>
<div align="left"><br>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">So when any row of "mtrl_issue_detail"
is inserted or updated this trigger is
fired on one function.</span></font></div>
<div align="left"><br>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">This function updates the same row
that has just been inserted or updated.</span></font></div>
<div align="left"><br>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">Now when this process happens the
database gets HANGED. and i have to
restart the database on server.</span></font></div>
<div align="left"><br>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">Is there any solution to do this process
without affecting my server.</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">********************************************************************************</span></font></div>
<div align="left"><font face="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() returns
opaque as'</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">DECLARE</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> rec RECORD;</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> tmp_rate REAL;</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> bl_amt REAL;</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> ex_amt REAL;</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> ad_amt REAL;</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> les_amt REAL;</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> t_amt REAL;</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> rel_per REAL;</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> rel_frac REAL;</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> tx_amt REAL;</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> ad_tax REAL;</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> gr_amt REAL;</span></font></div>
<div align="left"><br>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">BEGIN</span></font></div>
<div align="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"><span style="font-size:10pt"> WHERE
r.m_code = NEW.mtrl_code</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> AND
m.issue_no = NEW.issue_no</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> AND
r.effective_dt = (SELECT MAX(effective_dt) FROM rate_mst</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> WHERE
rate_mst.m_code=NEW.mtrl_code </span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> AND
rate_mst.effective_dt <= m.issue_dt);</span></font></div>
<div align="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"><span style="font-size:10pt"> tmp_rate
:= rec.trf_rate;</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> ELSIF rec.issue_type = ''con_sale''
THEN</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> tmp_rate
:= rec.con_rate;</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> ELSIF rec.issue_type = ''gen_sale''
THEN</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">
tmp_rate := rec.gen_rate_cs;</span></font></div>
<div align="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"><span style="font-size:10pt"> ELSE rec.issue_type = ''mrp_sale''
THEN</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> tmp_rate
:= rec.mrp_rate_cs;</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> END IF;</span></font></div>
<div align="left"><br>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> IF rec.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>
<div align="left"><font face="Arial"><span style="font-size:10pt"> rel_per := rec.cst_per;</span></font></div>
<div align="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>
<div align="left"><font face="Arial"><span style="font-size:10pt"> rel_per := rec.gst_per;</span></font></div>
<div align="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>
<div align="left"><font face="Arial"><span style="font-size:10pt"> tmp_rate
:= tmp_rate;</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> rel_per := 0;</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> rel_frac
:= 0;</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> END IF;</span></font></div>
<div align="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"><font face="Arial"><span style="font-size:10pt"> ad_amt := NEW.issueqty_cs
* rec.add_other_cs;</span></font></div>
<div align="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"><span style="font-size:10pt"> t_amt := CAST((bl_amt
+ ex_amt + ad_amt - les_amt) AS REAL);</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> </span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> tx_amt := t_amt * (rel_per/100)
* (rel_frac/100);</span></font></div>
<div align="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"><font face="Arial"><span style="font-size:10pt"> </span></font><font face="Arial" color="#0000ff"><span
style="font-size:10pt">UPDATE mtrl_issue_detail </span></font></div>
<div align="left"><font face="Arial" color="#0000ff"><span style="font-size:10pt"> SET
rate_per_cs=tmp_rate,</span></font></div>
<div align="left"><font face="Arial" color="#0000ff"><span style="font-size:10pt"> bill_amt=bl_amt,</span></font></div>
<div align="left"><font face="Arial" color="#0000ff"><span style="font-size:10pt"> exise_amt=ex_amt,</span></font></div>
<div align="left"><font face="Arial" color="#0000ff"><span style="font-size:10pt"> add_amt=ad_amt,</span></font></div>
<div align="left"><font face="Arial" color="#0000ff"><span style="font-size:10pt"> less_amt=les_amt,</span></font></div>
<div align="left"><font face="Arial" color="#0000ff"><span style="font-size:10pt"> tot_amt=t_amt,</span></font></div>
<div align="left"><font face="Arial" color="#0000ff"><span style="font-size:10pt"> tax_amt=tx_amt,</span></font></div>
<div align="left"><font face="Arial" color="#0000ff"><span style="font-size:10pt"> add_tax=ad_tax,</span></font></div>
<div align="left"><font face="Arial" color="#0000ff"><span style="font-size:10pt"> gross_amt=gr_amt</span></font></div>
<div align="left"><font face="Arial" color="#0000ff"><span style="font-size:10pt"> WHERE
issue_no=NEW.issue_no</span></font></div>
<div align="left"><font face="Arial" color="#0000ff"><span style="font-size:10pt"> AND
mtrl_code=NEW.mtrl_code</span></font></div>
<div align="left"><font face="Arial" color="#0000ff"><span style="font-size:10pt"> AND
batch_code=NEW.batch_code;</span></font></div>
<div align="left"><br>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">RETURN NULL;</span></font></div>
<div align="left"><br>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">END;'</span></font></div>
<div align="left"><br>
</div>
<div align="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"><font face="Arial"><span style="font-size:10pt">TRIGGER : taxcal_trg</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">********************************************************************************</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">CREATE TRIGGER taxcal_trg </span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> AFTER INSERT OR UPDATE
ON mtrl_issue_detail</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> FOR
EACH ROW EXECUTE PROCEDURE taxcal_fun();</span></font></div>
<div align="left"><br>
</div>
<div align="left"><br>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">Whe the </span></font><font face="Arial" color="#0000ff"><span
style="font-size:10pt">BLUE </span></font><font face="Arial" color="#000000"><span style="font-size:10pt">part
is executed the problem arises</span></font></div>
<div align="left"><font face="Arial" color="#000000"><span style="font-size:10pt">and without this part
is runs smoothly.</span></font></div>
<div align="left"><br>
</div>
<div align="left"><font face="Arial" color="#000000"><span style="font-size:10pt">Is there any solution
update this </span></font><font face="Arial" color="#0000ff"><span style="font-size:10pt">query</span></font><font
face="Arial" color="#000000"><span style="font-size:10pt">.</span></font></div>
<div align="left"></div>
</body>