Re: After each row trigger NOT seeing data changes?

On May 21, 2009, at 10:31 PM, Rodrigo Gonzalez wrote:

> you have to change RETURN NULL; with RETURN NEW;

No he doesn't, it's an AFTER insert trigger; the data has already been
inserted at that point, so the return value is not relevant.

And don't top-post, please.

> On 05/21/2009 04:57 PM, Karl Nack wrote:
>> I'm attempting to create an inventory of trees. Here's a simplified,
>> sample table:
>>
>> CREATE TABLE tree (
>> tree_id SERIAL PRIMARY KEY,
>> tree_species_id INT NOT NULL REFERENCES tree_species,
>> tree_location POINT NOT NULL,
>> tree_install_date DATE NOT NULL,
>> tree_removal_date DATE,
>> CHECK (tree_removal_date > tree_install_date)
>> );
>>
>>
>> I need to ensure that no two trees are located in the same place at
>> the
>> same time:
>>
>> CREATE OR REPLACE FUNCTION check_unique_tree()
>> RETURNS trigger
>> AS $$
>> DECLARE
>> num_trees INT;
>> BEGIN
>> -- just to see what's going on
>> SELECT COUNT(tree_id) INTO num_trees FROM tree;
>> RAISE NOTICE '% % of new tree %, there are % trees.',
>> TG_WHEN, TG_OP, NEW, num_trees;
>>
>> PERFORM tree_id
>> FROM tree
>> WHERE
>> -- first condition prevents updated tree from matching with itself
>> NEW.tree_id <> tree_id
>> AND NEW.tree_location ~= tree_location
>> AND NEW.tree_install_date <
>> COALESCE(tree_removal_date, timestamp 'infinity')
>> AND COALESCE(NEW.tree_removal_date, timestamp 'infinity') >
>> tree_install_date;
>>
>> IF FOUND THEN
>> RAISE EXCEPTION 'Conflicting trees';
>> END IF;
>>
>> RETURN NULL;
>> END;
>> $$ LANGUAGE plpgsql STABLE;
>>
>>
>> CREATE TRIGGER check_unique_tree
>> AFTER INSERT OR UPDATE ON tree
>> FOR EACH ROW EXECUTE PROCEDURE check_unique_tree();
>>
>>
>> And yet, I'm able to do this:
>>
>> => INSERT INTO tree (tree_species_id, tree_location,
>> tree_install_date)
>> -> VALUES
>> -> (1, '(1,1)', 'today'),
>> -> (1, '(1,1)', 'today');
>> NOTICE: AFTER INSERT of new tree (20,1,"(1,1)",2009-05-21,), there
>> are 0
>> trees.
>> NOTICE: AFTER INSERT of new tree (21,1,"(1,1)",2009-05-21,), there
>> are 0
>> trees.
>> INSERT 0 2
>>
>>
>> As a sanity check (on a fresh, truncated table):
>>
>> => INSERT INTO tree (tree_species_id, tree_location,
>> tree_install_date)
>> -> VALUES (1, '(1,1)', 'today');
>> NOTICE: AFTER INSERT of new tree (22,1,"(1,1)",2009-05-21,), there
>> are 0
>> trees.
>> INSERT 0 1
>>
>> => INSERT INTO tree (tree_species_id, tree_location,
>> tree_install_date)
>> -> VALUES (1, '(1,1)', 'today');
>> NOTICE: AFTER INSERT of new tree (23,1,"(1,1)",2009-05-21,), there
>> are 1
>> trees.
>> ERROR: Conflicting trees
>>
>>
>> I notice the row count does not reflect the newly-inserted row, which
>> suggests that the trigger is not seeing changes made to the table.
>> This
>> seems to be exactly opposite of what's in the manual:
>> http://www.postgresql.org/docs/8.3/interactive/trigger-datachanges.html
>> http://www.postgresql.org/docs/8.3/interactive/trigger-example.html
>>
>> Am I doing something wrong here? Have I misunderstood the manual?
>> Have I
>> found a bug? Any help is greatly appreciated, as this check is pretty
>> key to what I'm trying to do.
>>
>> Thanks.
>>
>> Karl Nack
>>
>> Futurity, Inc.
>> 773-506-2007
>>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
>
>

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4a16764110091025167268!