✓ Kizspy.me Question 9:
Create a trigger named Tr1 for the INSERT statement on the "Medical Records" table so that
when we execute an insert statement to add one or more rows to the "Medical Records"
table, only valid rows will be inserted into the "Medical Records" table, all invalid rows will be
ignored. A row to be inserted is valid if all the following requirements are satisfied:
- The RecordDate of the inserted medical record is greater than or equal to the
AppointmentDate of the appointment corresponding to the inserted medical record.
- The PatientID of the inserted medical record is equal to the PatientID of the appointment
corresponding to the inserted medical record.
- The DoctorID of the inserted medical record is equal to the DoctorID of the appointment
corresponding to the inserted medical record.
For example, if we execute the following statements to insert 4 medical records and then
select these medical records from "Medical Records" table, we can see that only the first row
is inserted as shown in the following figure. The second row is not inserted because the
RecordDate is not valid, the third row is not inserted because the DoctorID is not valid and
the 4th row is not inserted because the PatientID is not valid.
insert into Medical Records (RecordID, PatientID, DoctorID, AppointmentID, Diagnosis,
Treatment, Notes, RecordDate)
values(200,16,9,28,'Migraine', 'Dietary changes','Diagnosed with Migraine', '2021-08-14
10:30:00.000'),
(201,7,19,45,'Arthritis', 'Regular monitoring', 'Monitoring', '2019-03-07 00:00:00.000'),
(202,40,21,60,'Flu', 'Medication', 'Medication', '2022-01-20 10:00:00.000'),
(203,58,1,70,'Flu', 'Medication', 'Medication', '2021-01-12 10:00:00.000')
select from Medical Records
where RecordID>= 200
RecordID PatientID DoctorID AppointmentID Diagnosis Treatment
200
16
9
28
Notes
RecordDate
Migraine Dietary changes Diagnosed with Migraine 2021-08-14 10:30:00.000
Zoom
+ 100%
Close
