Re: After each row trigger NOT seeing data changes?

you have to change RETURN NULL; with RETURN NEW;

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
>