INSERT INTO Patient@B
(par_patientNumb, par_SS, par_patientName, par_dateOfBirth, par_address, par_dateAdmitted, 'B');
INSERT INTO Pat_Doc_Treat@B
(par_patientNumb, par_doctorID, par_treatmentCode, par_treatmentDate, par_comments)
DELETE FROM Pat_Doc_Treat
WHERE patientNumb IN
(SELECT patientNumb FROM Patient
DELETE FROM Patient
And if there is a failure, there would be an EXCEPTION for ROLLBACK.
Assume that this procedure is used by a nurse/receptionist to transfer a patient from one clinic to another.
I was going to using the
INSERT INTO Patient Select ... but it wouldn't change the clinic to 'B' and the PatientNumb is a primary key so it can't be NULL.
Also, if the procedures were created separate...would there be any of the phenomena such as "dirty read", "fuzzy read", "phantom"?