[SOLVED] Trigger stopping appointments on a Sunday

Trigger stopping appointments on a Sunday

I need to write a trigger to stop users from booking appointments on a Sunday.
After setting a constraint to do this I realised that my coursework asks for it to be done with a trigger which I have been struggling to get working.

The trigger is currently being accepted without any errors but doesn't do anything when data is entered.

CREATE OR REPLACE TRIGGER no_sunday_appointments
BEFORE INSERT OR UPDATE ON Appointment
FOR EACH ROW
DECLARE
    AppointmentDate   DATE;
BEGIN
     IF (trim(TO_CHAR(AppointmentDate,'DAY')) = 'SUNDAY')
     THEN RAISE_APPLICATION_ERROR(-20100,
     'No Appointments Allowed On A Sunday');
 END IF;
END;

I have tried various changes such as not using the trim or using not like 'SUNDAY%' instead of = with no effect, or adding :new. before the trim TO_CHAR and AppointmentDate.

Solution

I am guessing that you want to access AppointmentDate in the table being modified. You have AppointmentDate declared as a variable and don't set its value -- so the value remains NULL. So:

CREATE OR REPLACE TRIGGER no_sunday_appointments
BEFORE INSERT OR UPDATE ON Appointment
FOR EACH ROW
BEGIN
     IF (trim(TO_CHAR(:new.AppointmentDate, 'DAY')) = 'SUNDAY') THEN
        RAISE_APPLICATION_ERROR(-20100, 'No Appointments Allowed On A Sunday');
     END IF;
END;

If it is not in the table directly, you might need the variable to calculate it.