Warning : Trigger created with compilation errors, SQL Statement ignored and invalid relational operator

Question

I want to limit input on my table using triggers, but I always get an error message. Warning: Trigger created with compilation errors.

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/1      PL/SQL: SQL Statement ignored
4/67     PL/SQL: ORA-00920: invalid relational operator

this is the code for my database table

CREATE TABLE jadwal(
    id_jadwal VARCHAR2(10) NOT NULL,
    hari VARCHAR2(7) NOT NULL,
    jam_masuk TIME,
    jam_keluar TIME,
    id_ajar VARCHAR2(20) NOT NULL,
    id_ruang VARCHAR2(4) NOT NULL,
    CONSTRAINT pk_jadwal PRIMARY KEY(id_jadwal),
    CONSTRAINT fk_ajar_jadwal FOREIGN KEY(id_ajar) REFERENCES
    ajar(id_ajar) ON DELETE CASCADE,
    CONSTRAINT fk_ruangan_jadwal FOREIGN KEY(id_ruang) REFERENCES
    ruangan(id_ruang) ON DELETE CASCADE
);

and this is the code for my trigger

CREATE OR REPLACE TRIGGER bef_ins_or_upd_jadwal
BEFORE INSERT OR UPDATE ON jadwal
FOR EACH ROW
DECLARE
    v_cek number(1);
BEGIN
    SELECT COUNT(id_jadwal) INTO v_cek FROM jadwal WHERE old.id_ruang := new.id_ruang AND old.hari := new.hari AND old.jam_masuk :<= new.jam_masuk AND old.jam_keluar :>= new.jam_masuk AND old.id_ajar := new.id_ajar
    OR
    old.id_ruang := new.id_ruang AND old.hari := new.hari AND old.jam_masuk :<= new.jam_keluar AND old.jam_keluar :>= new.jam_keluar AND old.id_ajar := new.id_ajar
    OR
    old.id_ruang := new.id_ruang AND old.hari := new.hari AND old.jam_masuk :>= new.jam_masuk AND old.jam_keluar :<= new.jam_keluar AND old.id_ajar := new.id_ajar;

    if v_cek > 0 THEN
        dbms_output.put_line('id_ruang Sudah Di Isi');
    ELSE
        dbms_output.put_line('berhasil');
    END IF;
END;

I use it to define greater or equal to – james Dec 03 '19 at 15:44 — james, Dec 03 '19 at 15:44
Well that's not valid Oracle syntax. Use `>=` and `<=`. – APC Dec 03 '19 at 15:51 — APC, Dec 03 '19 at 15:51
I have used it but got the same results – james Dec 03 '19 at 15:55 — james, Dec 03 '19 at 15:55

score 1 · Accepted Answer · answered Dec 03 '19 at 16:09

First of all, :<=, :>= are not valid Oracle syntax.

:= is also invalid when using inside a query. It's valid in some other cases like giving value to a variable like: this_var := 5;

Secondly, according to Oracle documentation:

To reference a pseudorecord, put a colon before its name—:OLD or :NEW

This means if you want to access let's say old.id_ruang, you should access it like: :old.id_ruang

With all this being said the code should look like:

CREATE OR REPLACE TRIGGER bef_ins_or_upd_jadwal
BEFORE INSERT OR UPDATE ON jadwal
FOR EACH ROW
DECLARE
    v_cek number(1);
BEGIN
    SELECT COUNT(id_jadwal) INTO v_cek FROM jadwal WHERE :old.id_ruang = :new.id_ruang AND :old.hari = :new.hari AND :old.jam_masuk <= :new.jam_masuk AND :old.jam_keluar >= :new.jam_masuk AND :old.id_ajar = :new.id_ajar
    OR
    :old.id_ruang = :new.id_ruang AND :old.hari = :new.hari AND :old.jam_masuk <= :new.jam_keluar AND :old.jam_keluar >= :new.jam_keluar AND :old.id_ajar = :new.id_ajar
    OR
    :old.id_ruang = :new.id_ruang AND :old.hari = :new.hari AND :old.jam_masuk >= :new.jam_masuk AND :old.jam_keluar <= :new.jam_keluar AND :old.id_ajar = :new.id_ajar;

    IF v_cek > 0 THEN
        dbms_output.put_line('id_ruang Sudah Di Isi');
    ELSE
        dbms_output.put_line('berhasil');
    END IF;
END;

thank you very much, I guess (:) put before the operator. I have to read a lot and learn more. — james, Dec 03 '19 at 16:19

Warning : Trigger created with compilation errors, SQL Statement ignored and invalid relational operator

1 Answers1