Re: Problem with trigger makes Detail record be invalid

On 04/19/2018 06:49 PM, PegoraroF10 wrote:
On Fri, Apr 20, 2018 at 6:55 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

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

Such responsibility is an artifact of our specific implementation and not
an inherent property of writing triggers in the presence of FK constraints.

We've left a foot-gun laying around and should not be surprised when less
experienced users pick it up and shoot themselves in the foot.

IOW, I do agree with the OP - its just an unfortunate reality that this
isn't how things work today. Whether one can accept and work within this
reality is a personal decision.

This does reinforce that testing the restoration of ones backups is
important.

David J.