Forum OpenACS Q&A: can't delete a record in ec_custom_product_field_values

I just encountered an extremely weird issue with Ecommerce 4.x.

I can add products in two ways - a bulk upload via the upload
utilities and by using the Add product features in Ecommerce.

Someone noticed that they can delete some products while other
products would return a referrential integrity error when attempting
to delete.

After several hours of trying to figure out what was wrong with the
code I looked into the database and started manually executing the
qurries in the delete.tcl script. It so happened that I was trying to
delete a product I added using the add product feature. I tried

"delete from ec_custom_product_field_values where product_id=8523"

It returned

DELETE 0

which seems impossible because I have a record there.

Then I got a little frustrated and tried

"delete from ec_custom_product_fields"

which would have delete everything inside the table
but lo and behold. All the records are still intact when I did a

"select * from ec_custom_product_fields"

But then I started to delete a product which was added via the upload
utilities and it worked.

Has anybody encountered this strange phenomenon before ? I am
thinking this could be a postgres issue - I am using PG7.1 but then
again it could be a trigger or something like that which I am not
seeing.

Thanks,

Thanks for the swift response.

Below is the only trigger I found that deals with ec_custom_product_field_values

create function ec_custom_p_f_values_audit_tr ()
returns opaque as '
begin
        insert into ec_custom_p_field_values_audit (
        product_id,
        last_modified,
        last_modifying_user, modified_ip_address
        ) values (
        old.product_id,
        old.last_modified,
        old.last_modifying_user, old.modified_ip_address
        );
	return new;
end;' language 'plpgsql';

create trigger ec_custom_p_f_values_audit_tr
after update or delete on ec_custom_product_field_values
for each row execute procedure ec_custom_p_f_values_audit_tr();

I noticed that the triger is "after update or delete". I'm going to experiment with the above and see if it solves my problem or it might create a new one.

Thanks Again.

/Bart

I could not find the SDM for the Ecommerce 4.x package. Should I put the patch in the Ecommerce (ACS 3 Based) ?

Anyways here is what fixed it. Thanks to Dan for helping me out on the trigger.

The culprit is in package/ecommerce/sql/postgresql/ecommerce-create.tcl

create function ec_custom_p_f_values_audit_tr ()
returns opaque as '
begin
        insert into ec_custom_p_field_values_audit (
        product_id,
        last_modified,
        last_modifying_user, modified_ip_address
        ) values (
        old.product_id,
        old.last_modified,
        old.last_modifying_user, old.modified_ip_address
        );
		if TG_OP = ''INSERT'' then
			return new;
		else
			return old;
		end if;
end;' language 'plpgsql';

create trigger ec_custom_p_f_values_audit_tr
after update or delete on ec_custom_product_field_values
for each row execute procedure ec_custom_p_f_values_audit_tr();

Thanks Again.

Thanks again,