Обсуждение: Referencing external table in update/insert triggers
Hi<br /><br />I have the following table structure for which I am trying to set a AFTER INSERT or UPDATE trigger:<br /><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.t1f4 where t2f1=NEW.d1;<br /> RETURN NEW;<br/> END IF;<br />END;<br />$update_data$ LANGUAGE plpgsql;<br /><br />[t1]<br />t1f1 integer <br />t1f2 integer<br/>t1f3 integer<br /> t1f4 timestamp without time zone<br />t1f5 character varying<br />t1f6 real<br />d1 bigint [fk: t2->t2f1]<br /><br />CREATE TRIGGER test_trigger AFTER INSERT OR UPDATE ON t1 FOR EACH ROW EXECUTE PROCEDUREupdate_data();<br /><br /> [t2]<br />t2f1 integer NOT NULL<br />t2f2 integer<br />t2f3 integer<br />t2f4 timestampwithout time zone<br /><br />I would like to compare the date present in the t2f4 with the new data being updatedthrough the trigger. Using the if line as listed above returns an error. Is it possible to reference table t2 withinthe same trigger? I could also use another trigger (BEFORE UPDATE) on t2 to achieve the same, but would like to avoidit.<br /><br />With regards<br /><br />Amitabh<br />
On 17/02/10 15:18, Amitabh Kant wrote: > > CREATE OR REPLACE FUNCTION update_data() RETURNS TRIGGER AS $update_data$ > BEGIN > IF NEW.t1f4> t2.t2f4 > UPDATE t2 set t2f2=NEW.t1f2, t2f3=NEW.t1f3, t2f4=NEW.t1f4 where > t2f1=NEW.d1; > RETURN NEW; > END IF; > END; > $update_data$ LANGUAGE plpgsql; > I would like to compare the date present in the t2f4 with the new data being > updated through the trigger. Using the if line as listed above returns an > error. You can always do something like: SELECT t2.t2f4 INTO my_variable FROM t2 WHERE ... IF NEW.t1f4 > my_variable However, for this case you can just do an update with an extended where clause: UPDATE t2 ... WHERE t2f1=NEW.d1 AND NEW.t1f4 > t2.t2f4 No need for the IF. -- Richard Huxton Archonet Ltd
<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 />