Some one plz help me (MAHENDRA D RATHOD)
| От | Ramesh PAtel |
|---|---|
| Тема | Some one plz help me (MAHENDRA D RATHOD) |
| Дата | |
| Msg-id | 3EBFCA5E.28219.12E5A36@localhost обсуждение исходный текст |
| Ответы |
Re: Some one plz help me (MAHENDRA D RATHOD)
|
| Список | pgsql-admin |
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.
Is there any solution to do this process without affecting my server.
********************************************************************************
FUNCTION: taxcal_fun()
********************************************************************************
CREATE FUNCTION taxcal_fun() returns opaque as'
DECLARE
rec RECORD;
tmp_rate REAL;
bl_amt REAL;
ex_amt REAL;
ad_amt REAL;
les_amt REAL;
t_amt REAL;
rel_per REAL;
rel_frac REAL;
tx_amt REAL;
ad_tax REAL;
gr_amt REAL;
BEGIN
SELECT INTO rec * FROM rate_mst r, mtrl_issue_mst m
WHERE r.m_code = NEW.mtrl_code
AND m.issue_no = NEW.issue_no
AND r.effective_dt = (SELECT MAX(effective_dt) FROM rate_mst
WHERE rate_mst.m_code=NEW.mtrl_code
AND rate_mst.effective_dt <= m.issue_dt);
IF rec.issue_type = ''stk_trf_sale'' THEN
tmp_rate := rec.trf_rate;
ELSIF rec.issue_type = ''con_sale'' THEN
tmp_rate := rec.con_rate;
ELSIF rec.issue_type = ''gen_sale'' THEN
tmp_rate := rec.gen_rate_cs;
ELSIF rec.issue_type = ''agent_sale'' THEN
tmp_rate := rec.soc_rate_cs;
ELSE rec.issue_type = ''mrp_sale'' THEN
tmp_rate := rec.mrp_rate_cs;
END IF;
IF rec.tax_code = ''c'' OR rec.tax_code = ''C'' THEN
tmp_rate := tmp_rate + CAST(round(tmp_rate * rec.cst_per / 100,2) AS REAL);
rel_per := rec.cst_per;
rel_frac := rec.cst_frac;
ELSIF rec.tax_code = ''g'' or rec.tax_code = ''G'' THEN
tmp_rate := tmp_rate + CAST(round(tmp_rate * rec.gst_per / 100,2) AS REAL);
rel_per := rec.gst_per;
rel_frac := rec.gst_frac;
ELSE
tmp_rate := tmp_rate;
rel_per := 0;
rel_frac := 0;
END IF;
bl_amt := CAST((NEW.issueqty_cs * tmp_rate) AS REAL);
ex_amt:= bl_amt*(rec.exise_per/100)*(rec.exise_frac/100);
ad_amt := NEW.issueqty_cs * rec.add_other_cs;
les_amt := cast((NEW.issueqty_mt * 1000 * rec.less_other_kg) AS REAL);
t_amt := CAST((bl_amt + ex_amt + ad_amt - les_amt) AS REAL);
tx_amt := t_amt * (rel_per/100) * (rel_frac/100);
ad_tax := tx_amt * (rec.additional_tax/100) * (rec.additional_frac/100);
gr_amt := t_amt + tx_amt + ad_tax;
UPDATE mtrl_issue_detail
SET rate_per_cs=tmp_rate,
bill_amt=bl_amt,
exise_amt=ex_amt,
add_amt=ad_amt,
less_amt=les_amt,
tot_amt=t_amt,
tax_amt=tx_amt,
add_tax=ad_tax,
gross_amt=gr_amt
WHERE issue_no=NEW.issue_no
AND mtrl_code=NEW.mtrl_code
AND batch_code=NEW.batch_code;
RETURN NULL;
END;'
LANGUAGE 'plpgsql';
********************************************************************************
TRIGGER : taxcal_trg
********************************************************************************
CREATE TRIGGER taxcal_trg
AFTER INSERT OR UPDATE ON mtrl_issue_detail
FOR EACH ROW EXECUTE PROCEDURE taxcal_fun();
Whe the BLUE part is executed the problem arises
and without this part is runs smoothly.
Is there any solution update this query.
В списке pgsql-admin по дате отправления: