On 04/19/2018 06:49 PM, PegoraroF10 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.
