Search code examples
oracle-databasesyntaxplsqldate-comparisondatabase-trigger

How to check if time is greater or less than another time in PLSQL


I am trying to check in my trigger if an inserted record is in the table TUTPRAC contains and CLASSTIME < '9AM' OR > '6PM'. If this is true then that record is updated with certain fields being changed to NULL.

CREATE TRIGGER CheckBeforeAfterHours
BEFORE INSERT OR UPDATE OF CLASS_TIME ON TUTPRAC
FOR EACH ROW
BEGIN
  IF (:NEW.CLASS_TIME < '09:00' OR > '18:00') THEN
     :NEW.STAFFNO := NULL;
     :NEW.CLASS_DAY := NULL;
     :NEW.CLASS_TYPE := NULL;
     :NEW.ROOMNUM := NULL;
  END IF;
END CheckBeforeAfterHours;

Columns of TUTPRAC table:

CLASSID (PK), UNITCODE, STAFFNO, CLASSDAY, CLASSTIME, CLASSTYPE, ROOMNUM

The field CLASSTIME is set to varchar(5).

I use Oracle SQLDeveloper.

PROBLEM

my issue is that i keep getting this error when i try to run the trigger:

Error(2,36):
PLS-00103: Encountered the symbol ">" when expecting one of the following:
    ( - + case mod new not null <an identifier> <a double-quoted delimited-identifier>
    <a bind variable> continue avg count current exists max min prior sql stddev
    sum variance execute forall merge time timestamp interval
    date <a string literal with character set specification>
    <a number> <a single-quoted SQL string> pipe
    <an alternatively-quoted string literal with character set specification>
    <an alternatively

Solution

  • The correct syntax is:

    IF (:NEW.CLASS_TIME < '09:00' OR :NEW.CLASS_TIME > '18:00') THEN