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(dot)klaver(at)aklaver(dot)com