Re: Referencing external table in update/insert triggers

<br /><br /><div class="gmail_quote">On Thu, Feb 18, 2010 at 2:53 PM, Richard Huxton <span dir="ltr"><<a
href="mailto:dev@archonet.com">dev@archonet.com</a>></span>wrote:<br /><blockquote class="gmail_quote"
style="margin:0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"><div class="im">On
17/02/1015:18, Amitabh Kant wrote:<br /><blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left:
1pxsolid rgb(204, 204, 204); padding-left: 1ex;"><br /> CREATE OR REPLACE FUNCTION update_data() RETURNS TRIGGER AS
$update_data$<br/> BEGIN<br />     IF NEW.t1f4>  t2.t2f4<br />         UPDATE t2 set t2f2=NEW.t1f2, t2f3=NEW.t1f3,
t2f4=NEW.t1f4where<br /> t2f1=NEW.d1;<br />         RETURN NEW;<br />     END IF;<br /> END;<br /> $update_data$
LANGUAGEplpgsql;<br /></blockquote><br /></div><div class="im"><blockquote class="gmail_quote" style="margin: 0pt 0pt
0pt0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"> I would like to compare the date present in
thet2f4 with the new data being<br /> updated through the trigger.  Using the if line as listed above returns an<br />
error.<br/></blockquote><br /></div> You can always do something like:<br />  SELECT t2.t2f4 INTO my_variable FROM t2
WHERE...<br />  IF NEW.t1f4 > my_variable<br /><br /> However, for this case you can just do an update with an
extendedwhere clause:<br />  UPDATE t2 ... WHERE t2f1=NEW.d1 AND NEW.t1f4 > t2.t2f4<br /><br /> No need for the
IF.<br/><font color="#888888"><br /> -- <br />  Richard Huxton<br />  Archonet Ltd<br /></font></blockquote></div><br
/><br/>Thanks Richard. <br /><br />With regards<br /><br />Amitabh Kant<br />