I have two databases that effect each other when triggers get excecuted.
There is a schedules database that updates registration database. The
problem I have is with the enrolled, attended, waitlisted, completed,
cancelled, etc. booleans values. The registration db has triggers on it that
enforce certain logical rules to be enforced to ensure that logical
registration statuses get correctly (Ex. the user of the database should be
enrolled=true if cancelled=true)
The problem I have is that, ON schedule UPDATEs, the registration data gets
defaulted back to enrolled = true even if pre-existing regitration data in
the db is already set to say attend, or completed status. I will paste the
two triggers below if you might just notic something wrong with the logic
causing this unexpected behavior when the trigger defaults pre-existing
registration data to match the enrolled state:
CREATE TRIGGER trigger_on_schedule_updates
AFTER UPDATE
ON schedules
FOR EACH ROW
BEGIN
UPDATE registration_and_attendance SET
class_id = new.class_id,
start_date = new.start_date,
end_date = new.end_date
WHERE schedule_id = new.id;
END;
CREATE TRIGGER trigger_registration_and_attendance_before_update
BEFORE UPDATE
ON registration_and_attendance
FOR EACH ROW
BEGIN
IF (new.enrolled = true) THEN
SET new.attended = false;
SET new.completed = false;
SET new.waitlisted = false;
SET new.cancelled = false;
END IF;
IF (new.attended = true) THEN
SET new.enrolled = true;
SET new.waitlisted = false;
SET new.completed = false;
SET new.cancelled = false;
END IF;
IF (new.completed = true) THEN
SET new.enrolled = true;
SET new.attended = true;
SET new.waitlisted = false;
SET new.cancelled = false;
END IF;
IF (new.waitlisted = true) THEN
SET new.enrolled = false;
SET new.attended = false;
SET new.completed = false;
SET new.cancelled = false;
END IF;
IF (new.cancelled = true) THEN
SET new.enrolled = false;
SET new.attended = false;
SET new.completed = false;
SET new.waitlisted = false;
SET new.overflow_registrant = false;
END IF;
END;
Ferindo
--
justferindo
