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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com