Re: row not deleted but updated (trigger?)

From: "Igor Neyman" <ineyman(at)perceptron(dot)com>
To: "Ivan Sergio Borgonovo" <mail(at)webthatworks(dot)it>, <pgsql-sql(at)postgresql(dot)org>
Subject: Re: row not deleted but updated (trigger?)
Date: 2009-02-27 15:06:56
Message-ID: F4C27E77F7A33E4CA98C19A9DC6722A204218178@EXCHANGE.corp.perceptron.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

"delete" trigger should return "old".
In your code you return "new" for both: "update" and "delete"

Igor

-----Original Message-----
From: pgsql-sql-owner(at)postgresql(dot)org
[mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Ivan Sergio
Borgonovo
Sent: Friday, February 27, 2009 6:56 AM
To: pgsql-sql(at)postgresql(dot)org
Subject: [SQL] row not deleted but updated (trigger?)

I've:

create or replace function FT1IDX_catalog_brands_update() returns
trigger as $$ begin
if(TG_OP='DELETE') then
update catalog_items set
FT1IDX=GetFTIDX('pg_catalog.english', Code, CodeAlt, ISBN, Name,
Authors, '')
where BrandID=old.BrandID;
else
if(coalesce(new.Name,'')<>coalesce(old.Name,'')) then
update catalog_items set
FT1IDX=GetFTIDX('pg_catalog.english', Code, CodeAlt, ISBN,
Name, Authors, new.Name)
where BrandID=new.BrandID;
end if;
end if;
return new;
end $$ language plpgsql volatile;

create trigger FT1IDX_catalog_brands_update_trigger before update or
delete on catalog_brands for each row execute procedure
FT1IDX_catalog_brands_update();

I do something
update catalog_brands set name='zzz' where brandid=1234;
1 row get updated.

When I do
delete from catalog_brands where brandid=1234; no row get deleted and no
error get reported.

what did I miss?

thanks

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Carol Cheung 2009-02-27 21:02:00 counts of groupings by date year-month
Previous Message Ivan Sergio Borgonovo 2009-02-27 12:21:49 Re: row not deleted but updated (trigger?)