Re: Affected # of Rows After TRIGGER/RULE Return

On Wednesday 14 May 2008 02:59:27 Volkan YAZICI wrote:
>       RETURN NULL;
<snip>

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

something to try (whether it's acceptable or not will depend on
requirements that you didn't mention in your original post).

Instead of trying to update the row in place, insert the row again,
but with the field you need to mangle (in your example, "plate",
in my example below, "k") already mangled.  this only works if
the field you're mangling is the primary key.  if some other field
is the primary key, you will need to mangle that too, if possible.
since you'll have already inserted the row, just return OLD and let
the old row actually be deleted.

there are other options but the above works pretty well unless
you have constraints that make it impossible.

here's an example:

drop table t cascade;

create table t(k text primary key,oldk text);

create or replace function t_trig_func() RETURNS TRIGGER
as $$
BEGIN
    raise notice '%',old.k;
    insert into t(k,oldk) values (OLD.k||'-DEL',OLD.k);
    return OLD;
END;
$$ language plpgsql;

create trigger t_trig AFTER delete on t
    for each row execute procedure t_trig_func();

insert into t values ('1','1');
insert into t values ('2','2');
insert into t values ('3','3');
insert into t values ('4','4');

delete from t where k=4;
select * from t;

when I run that, the delete gives:

NOTICE:  4
DELETE 1
Time: 0.888 ms

(the notice is from the raise notice in the trigger function).

tiger

--
Gerald Timothy Quimpo   bopolissimus@gmail.com
   It is no measure of health to be well adjusted to a profoundly sick
    society.
         -- Krishnamurti