Re: Problem with trigger makes Detail record be invalid - Mailing list pgsql-general

On 04/19/2018 10:55 AM, PegoraroF10 wrote:
> I´m using Postgres 10 on ubuntu.
> 
> suppose a simple Master/Detail structure like this:
> 
> create table Master(ID integer primary key, name text);
> create table Detail(ID integer primary key, Master_ID Integer, OtherInfo
> text);
> alter table Detail add constraint FKMasterDetail foreign key (Master_ID)
> references Master(ID) on update cascade on delete cascade;
> 
> Then insert some records on it:
> insert into Master(ID, Name) values(1,'First'), values(2,'Second');
> insert into Detail(ID, Master_ID, OtherInfo) values(1,1,'Detail
> Information'), (2,2,'Detail Information2');
> 
> Then, if I delete on Master will delete on detail too. Fine.
> delete from Master where ID=1;
> 
> But now, suppose I have a complex trigger before update or delete that runs
> on Detail table.
> create function DoAComplexJobOnDetail() returns trigger as $$
> begin
>    -- Do lots of things then
>    Return new; --This is the error, because I´m returning new even for
> delete;

That can be dealt with using TG_OP value to conditionally change what is 
RETURNed:

https://www.postgresql.org/docs/10/static/plpgsql-trigger.html

"TG_OP

     Data type text; a string of INSERT, UPDATE, DELETE, or TRUNCATE 
telling for which operation the trigger was fired.
"

See Example 42.4. A PL/pgSQL Trigger Procedure For Auditing at bottom of 
page.

> end;$$ language plpgsql;
> create trigger DetailDoAComplexJob before update or delete on Detail for
> each row execute procedure DoAComplexJobOnDetail();
> 
> Then try to delete the other Master record. It will be deleted on Master but
> Detail record doesn´t and will obviously become invalid because the foreign
> key.
> delete from Master where ID=2;
> select * from Master; --will show no records.
> select * from Detail; --will show one record pointing to Master_ID=2, that
> doesn´t exist anymore.
> 
> Is this a bug or it´s mine responsability to check that trigger result ?

Without seeing exactly what the trigger function on Detail is doing that 
is not answerable.

> If that trigger responds incorrectly I think that no information could be
> executed.
> 
> 
> 
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com