Thread: Row insertion w/ trigger to another table update causes row insertion to _not_ occur

I think I'm doing this wrongly. Before I go out re-invent the wheel, I
thought I'll just check w/ the list. (I previously got the idea from IRC)

Table "Master"
--> Table "Child1"
--> Table "Child2"
....
--> Table "Child2"
Table "Update" --> Table to update come key items from source table.


The Master table is populated with some huge amount of data on a per minute
and based on date_ranges, it is diverted to the different child tables.
(classic partitioning)

What I'm trying to do here is to consolidate the (subset of) whole list of
data inserted into the individual child tables into a table called "update"

I tried it via a trigger function which is called "BEFORE INSERT" (I also
tried "AFTER INSERT")

But what happens is that once the insertion to the child table is in
progress, the update_table_trigger is fired, and the Update table gets
updated, _but_ the child table is un-touched.


How do I go about this? As of right now, I've turned off the triggers.


CREATE OR REPLACE FUNCTION update_table_trigger()
  RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO update
SELECT NEW.uniq_id,
NEW.start_date_time,
NEW.week_id
WHERE NOT EXISTS (  SELECT 1 FROM update WHERE uniq_id=NEW. uniq_id
  AND start_date_time=NEW.start_date_time
  AND week_id = NEW.week_id
  );
    RETURN NULL;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION head_raw_prod_sn_trigger() OWNER TO "operator";

On Tue, Jul 21, 2009 at 11:25 PM, Ow Mun Heng<ow.mun.heng@wdc.com> wrote:
>    RETURN NULL;


From the docs:
"It can return NULL to skip the operation for the current row."
-- http://www.postgresql.org/docs/current/static/trigger-definition.html

Just make your trigger return NEW and it won't kill the insert to the
child table.

--
greg
http://mit.edu/~gsstark/resume.pdf

-----Original Message-----
From: gsstark@gmail.com [mailto:gsstark@gmail.com] On Behalf Of Greg Stark
>> On Tue, Jul 21, 2009 at 11:25 PM, Ow Mun Heng<ow.mun.heng@wdc.com> wrote:
>>    RETURN NULL;


> From the docs:
> "It can return NULL to skip the operation for the current row."
> -- http://www.postgresql.org/docs/current/static/trigger-definition.html

> Just make your trigger return NEW and it won't kill the insert to the
> child table.

Dang it.. I just re-read the online docs this morning and I missed it.
Then again, since I'm new to triggers, I may have mis-interpreted that.

Thanks for it. I'll give it a go.

<p style="margin-bottom: 0cm; margin-top: 0pt;">Ow Mun Heng wrote:<br /><blockquote
cite="mid:D1109E8B2FB53A45BDB60F8145905CE902DB3D81@wdmyexbe03.my.asia.wdc.com"type="cite"><pre wrap="">-----Original
Message-----
From: <a class="moz-txt-link-abbreviated" href="mailto:gsstark@gmail.com">gsstark@gmail.com</a> [<a
class="moz-txt-link-freetext"href="mailto:gsstark@gmail.com">mailto:gsstark@gmail.com</a>] On Behalf Of Greg Stark
</pre><blockquotetype="cite"><blockquote type="cite"><pre wrap="">On Tue, Jul 21, 2009 at 11:25 PM, Ow Mun Heng<a
class="moz-txt-link-rfc2396E"href="mailto:ow.mun.heng@wdc.com"><ow.mun.heng@wdc.com></a> wrote:
 
   RETURN NULL;     </pre></blockquote></blockquote><blockquote type="cite"><pre wrap="">Just make your trigger return
NEWand it won't kill the insert to the
 
child table.   </pre></blockquote><pre wrap="">
Dang it.. I just re-read the online docs this morning and I missed it. 
Then again, since I'm new to triggers, I may have mis-interpreted that.

Thanks for it. I'll give it a go.</pre></blockquote> I agree that is certainly one problem, but in any case an After
Updatetrigger would have worked.<br /> 

> From: Sim Zacks [mailto:sim@compulab.co.il]

>-----Original Message-----
>From: gsstark@gmail.com [mailto:gsstark@gmail.com] On Behalf Of Greg Stark
>
>On Tue, Jul 21, 2009 at 11:25 PM, Ow Mun Heng<ow.mun.heng@wdc.com> wrote:
>   RETURN NULL;
>
>Just make your trigger return NEW and it won't kill the insert to the
>child table.

>>I agree that is certainly one problem, but in any case an After Update
>>trigger would have worked.

I actually did try that, it didn't work. (I thought I mentioned that in the
original email. Oh well..)

Have yet to try the NEW method though