Re: Trigger difference in 9.0 and 9.3

Erwan Tanajaya <erwan(dot)tanajaya(at)gmail(dot)com> wrote:

>> Aside from that a self-contain and reasonably faithful
>> reproduction of your exact problem might help you either obtain
>> advice or make Kevin et. al. reconsider the solution put in
>> place to avoid the problem they were facing.

> I really hope Kevin and other dev have a better solution for
> this case

You really need to provide a self-contained test case to
demonstrate what you think the problem is. For example, you might
think something like this works:

------------------------------------------------------------

create table tran_header
(
tran_id int not null,
total_amt int not null default (0),
primary key (tran_id)
);
create table tran_detail
(
tran_id int not null,
tran_det_id int not null,
amt int not null,
primary key (tran_id, tran_det_id)
);

create function tran_detail_insert_func()
returns trigger
language plpgsql
as $$
begin
update tran_header
set total_amt = total_amt + new.amt
where tran_header.tran_id = new.tran_id;
return new;
end;
$$;
create function tran_detail_update_func()
returns trigger
language plpgsql
as $$
begin
if old.tran_id = new.tran_id then
update tran_header
set total_amt = total_amt + new.amt - old.amt
where tran_header.tran_id = old.tran_id;
else
update tran_header
set total_amt = total_amt - old.amt
where tran_header.tran_id = old.tran_id;
update tran_header
set total_amt = total_amt + new.amt
where tran_header.tran_id = new.tran_id;
end if;
return new;
end;
$$;
create function tran_detail_delete_func()
returns trigger
language plpgsql
as $$
begin
update tran_header
set total_amt = total_amt - old.amt
where tran_header.tran_id = old.tran_id;
return old;
end;
$$;
create function tran_header_delete_func()
returns trigger
language plpgsql
as $$
begin
delete from tran_detail where tran_id = old.tran_id;
return old;
end;
$$;

create trigger tran_detail_insert_trig
after insert on tran_detail
for each row execute procedure tran_detail_insert_func();
create trigger tran_detail_update_trig
after update on tran_detail
for each row execute procedure tran_detail_update_func();
create trigger tran_detail_delete_trig
after delete on tran_detail
for each row execute procedure tran_detail_delete_func();
create trigger tran_header_delete_trig
before delete on tran_header
for each row execute procedure tran_header_delete_func();

insert into tran_header (tran_id) values (1), (2), (3);
insert into tran_detail (tran_id, tran_det_id, amt) values
(1,1,100),(1,2,200),(2,1,100),(2,2,200),(2,3,300);

select * from tran_header order by tran_id;

delete from tran_header where tran_id = 2;

------------------------------------------------------------

The above probably doesn't do what you expect or intend on 9.0.
Try a select from tran_header and you will notice that the row you
probably *thought* you deleted is still there. It is silently
doing the wrong thing. You can fix it like this:

------------------------------------------------------------

create or replace function tran_header_delete_func()
returns trigger
language plpgsql
as $$
begin
delete from tran_detail where tran_id = old.tran_id;
if found then
delete from tran_header where tran_id = old.tran_id;
return null;
end if;
return old;
end;
$$;

------------------------------------------------------------

You could also have fixed it by deleting the header row *twice*,
since (unless there is some other process adding detail in between)
there won't be any detail the second time to update the header row
and cause a problem -- what the above fix does it to do that second
delete automatically.

What happens in more recent versions is that rather than silently
failing to delete the specified master row, it gives an error so
you can fix your broken code.

Now, if you can provide a self-contained test case (similar to the
above -- something that can run from an empty database to
demonstrate your issue), we can offer other advice or consider some
adjustment to the code if it seems warranted. I'm pretty sure we
will not go back to the type of failure shown above.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company