Parcel #v926h9fmsgb88s0

DELIMITER //

CREATE TRIGGER patient_after_update_pregnant
AFTER UPDATE ON patient
FOR EACH ROW
BEGIN
    IF NEW.is_pregnant = True AND OLD.is_pregnant = False THEN
        -- Add pre-natal vitamin recommendation
        INSERT INTO recommendation (patient_id, message)
        VALUES (NEW.patient_id, 'Take pre-natal vitamins');
        
        -- Delete prescriptions for medications not safe during pregnancy
        DELETE FROM prescription
        WHERE patient_id = NEW.patient_id
        AND medication_id IN (
            SELECT medication_id
            FROM medication
            WHERE take_if_pregnant = False
        );
    ELSEIF NEW.is_pregnant = False AND OLD.is_pregnant = True THEN
        -- Remove pre-natal vitamin recommendation
        DELETE FROM recommendation
        WHERE patient_id = NEW.patient_id
        AND message = 'Take pre-natal vitamins';
    END IF;
END //

DELIMITER ;