Hi. I'm fruitlessly banging my head against a trigger. Which is
causing an 'update' to fail. Which is not my aim. There is nothing
obvious wrong, but maybe a member of this group can see something I cannot.
It does not return any error, and replies on the command line with the
usual:
=# UPDATE 1
Yet no update has taken place. Something about my trigger is messing it up.
My aim is to update my relation 'orders' with a summary of it's child
relation 'item'.
Brefly (and in lower case, sorry):
create table orders (
code serial8 not null primary key,
sum_items smallint not null default 0, -- Summary field
) ;
create table item (
orders integer not null,
descript varchar(50) not null default '',
primary key (orders, descript),
foreign key (orders) references orders (code) on delete cascade,
items smallint not null default 0, -- Source field
) ;
CREATE FUNCTION t_dec_item_summary ()
RETURNS trigger
AS '
BEGIN
update orders set
item_count = item_count - 1
WHERE code = OLD.orders;
RETURN OLD;
END;
' language 'plpgsql';
create trigger item_00_change
before delete or update
on item for each row
execute procedure t_dec_item_summary ();
Example:
=# select orders, descript, items from item ;
1 1 1
=# update item set items = 2 where orders = 1 and descript = '1' ;
UPDATE 1
=# select orders, descript, items from item ;
1 1 1
Therefore, no difference. The command appears to have failed.
Drop the trigger:
=# drop trigger item_00_change ;
=# update item set items = 2 where orders = 1 and descript = '1' ;
UPDATE 1
=# select orders, descript, items from item ;
1 1 2
And it will work. It will also completelly works on 'delete', which
calls the same trigger.
I do know with certainty that the trigger has fired.
What is there about my trigger is causing the command to fail without
reporting an error?
Something about my function t_dec_item_summary is causing the UPDATE to
fail to update the values, or update with the same values as already
existed.
Am I returning the correct thing? Should I force a return of 'TRUE' or
'FALSE' or something?
Ever thankful of amazing help from this group,
Ben