Re: Problem with trigger makes Detail record be invalid

On 04/20/2018 07:21 AM, David G. Johnston wrote:
> 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 <mailto: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.

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

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

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com