Trigger Problem

From: "Ramesh PAtel " <ramesh(at)banas(dot)guj(dot)nic(dot)in>
To: pgsql-novice(at)postgresql(dot)org
Subject: Trigger Problem
Date: 2003-04-22 08:27:08
Message-ID: 3EA54AC9.20255.CE8DCD@localhost
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

<body>
<div align="left"><font face="Arial"><span style="font-size:10pt">Hi All</span></font></div>
<div align="left"><br>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">i have problem in Trigger functoin</span></font></div>
<div align="left"><br>
</div>
<div align="left"><br>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">when trigger &nbsp;fire then ERROR
massage is</span></font></div>
<div align="left"><br>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">ERROR : &nbsp;Attribute 'UNDEF00'
not found</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">PostgreSQL status:PGRES_FATAL_ERROR</span></font></div>
<div align="left"><br>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">here UNDEF00 is by defult value of
po_no</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"><b>TRIGGER IS</b></span></font></div>
<div align="left"><br>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">CREATE TRIGGER add_stock_gr_trig &nbsp;AFTER
UPDATE on gr FOR EACH
ROW execute procedure add_stock_gr_fun(); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">my function is this</span></font></div>
<div align="left"><br>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">create function add_stock_gr_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"> &nbsp;&nbsp;&nbsp;rec RECORD; </span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> &nbsp;&nbsp;&nbsp;txtpo varchar; </span></font></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">IF NEW.po_no &lt;&gt; &quot;UNDEF00&quot;
THEN &nbsp;&nbsp;</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT
INTO rec &nbsp;gr_no, noofbags, final_wt, matl_cd </span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM
po p, gr g</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHERE
g.po_no=p.po_no and g.gr_no=NEW.gr_no; &nbsp;&nbsp;</span></font></div>
<div align="left"><br>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;IF
OLD.po_no #= &quot;UNDEF00&quot; THEN</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UPDATE
matl_mst </span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SET
bal_bag = bal_bag + rec.noofbags, &nbsp;</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;bal_kg = bal_kg + rec.final_wt &nbsp;</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHERE
matl_mst.matl_cd = rec.matl_cd; </span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ELSE</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UPDATE
matl_mst </span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SET
bal_bag = bal_bag + NEW.noofbags - OLD.noofbags, &nbsp;&nbsp;&nbsp;</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;bal_kg = bal_kg + NEW.final_wt &nbsp;-
OLD.final_wt</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHERE
matl_mst.matl_cd = rec.matl_cd; </span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;END
IF;</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">return NULL; &nbsp;&nbsp;&nbsp;</span></font></div>
<div align="left"><br>
</div>
<div align="left"><br>
</div>
<div align="left"><br>
</div>
<div align="left"><br>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">END;' &nbsp;language 'plpgsql'; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span></font></div>
<div align="left"><br>
</div>
<div align="left"><br>
</div>
<div align="left"><br>
</div>
<div align="left"><br>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">please Help me</span></font></div>
<div align="left"><br>
</div>
<div align="left"><br>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">Ramesh Patel</span></font></div>
<div align="left"></div>
</body>

Attachment Content-Type Size
Mail message body text/html 6.4 KB

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Dani Oderbolz 2003-04-22 08:50:36 Re: Trigger Problem
Previous Message Tom Lane 2003-04-22 01:36:04 Re: Installing postgresql