Affected # of Rows After TRIGGER/RULE Return

From: Volkan YAZICI <yazicivo(at)ttmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Affected # of Rows After TRIGGER/RULE Return
Date: 2008-05-13 14:59:27
Message-ID: 87skwm9rn4.fsf@alamut.mobiliz.com.tr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I'm trying to fake DELETEs to a table using below methods:

CREATE OR REPLACE FUNCTION mobileunit_fake_delete() RETURNS trigger AS $$
BEGIN
UPDATE mobileunit
SET networkid = OLD.networkid + OLD.muid * 100000000000,
groupid = 146688,
plate = 'DELETED_' || OLD.plate
WHERE muid = OLD.muid;

RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER mobileunit_fake_delete
BEFORE DELETE ON mobileunit
FOR EACH ROW EXECUTE PROCEDURE mobileunit_fake_delete();

or

CREATE RULE mobileunit_fake_delete
AS ON DELETE TO mobileunit
DO INSTEAD
UPDATE mobileunit
SET networkid = CAST(OLD.networkid AS numeric(20)) + OLD.muid * 100000000000,
groupid = 146688,
plate = 'DELETED_' || OLD.plate
WHERE muid = OLD.muid;

But unfortunately, both solutions make

DELETE FROM mobileunit WHERE muid = ...

queries return 0 as # of affacted rows. And this causes JDBC
applications (specifically Hibernate) ROLLBACK query as some failure
occured. At least, shouldn't the latter one return 1 as # of affected
rows? Any ideas to fix this problem?

Regards.

P.S. Yep, I know code sucks badly. Trying to migrate a Microsoft SQL
Server application to PostgreSQL.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Fetter 2008-05-13 15:14:51 Re: Making sure \timing is on
Previous Message Vivek Khera 2008-05-13 14:50:40 Re: Stripping out slony after / before / during pg_restore?