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.
-----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.
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html style="direction: ltr;">
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body style="direction: ltr;" bgcolor="#ffffff" text="#000000">
<p style="margin-bottom: 0cm; margin-top: 0pt;">Ow Mun Heng wrote:<br>
</p>
<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>
<blockquote type="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 NEW and 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 Update
trigger would have worked.<br>
</body>
</html>
From: Sim Zacks [mailto:sim@compulab.co.il]
-----Original Message-----
From: gsstark@gmail.com [mailto:gsstark@gmail.com] On Behalf Of Greg StarkOn 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