Re: Problem with trigger makes Detail record be invalid

On 04/19/2018 06:49 PM, PegoraroF10 wrote:
> Correct, that delete done a partial commit. And this is absolutely
> unacceptable.

Yet a known possible outcome. See the section on Triggers towards bottom 
of page:

https://en.wikipedia.org/wiki/Foreign_key

> I know I did that trigger incorrectly but referential integrity is
> obligatory.

I would agree if the FK relationship was entirely driven by the system 
trigger e.g:

alter table Detail add constraint FKMasterDetail foreign key (Master_ID)
references Master(ID) on update cascade on delete cascade;

As soon as you added your UPDATE/DELETE trigger you took on 
responsibility for how the data was passed around. I understand that 
this was not communicated as well as it should be in the docs.

> Imagine if I have a database crash and need to restore as soon as possible.
> How much time I´ll spend removing those records from a backup to get entire
> database restored properly.

Myself, having written more then my fair share of poorly thought out 
trigger  functions, I test new ones extensively before I release them 
into the wild.

> 
> Well, I´ll review all my triggers. And I have hundreds of them.
> 
> 
> 



-- 
Adrian Klaver
adrian.klaver@aklaver.com