Обсуждение: 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

On Fri, 27 Feb 2009 12:56:06 +0100
Ivan Sergio Borgonovo <mail@webthatworks.it> wrote:

> 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;

umpfs...
on delete new is null... so no action take place.
modified to return old on delete and new for the rest.
Sorry for the noise.

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

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

Igor

-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Ivan Sergio
Borgonovo
Sent: Friday, February 27, 2009 6:56 AM
To: pgsql-sql@postgresql.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


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql