oracle - 警告:使用编译错误创建触发器、忽略 SQL 语句和无效的关系运算符
问题描述
我想使用触发器限制表上的输入,但我总是收到一条错误消息。警告:使用编译错误创建触发器。
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/1 PL/SQL: SQL Statement ignored
4/67 PL/SQL: ORA-00920: invalid relational operator
这是我的数据库表的代码
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
);
这是我的触发器的代码
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;
解决方案
首先,:<=
,:>=
不是有效的 Oracle 语法。
:=
在查询中使用时也是无效的。它在其他一些情况下是有效的,比如给一个变量赋值,比如:this_var := 5;
其次,根据Oracle文档:
要引用伪记录,请在其名称前加一个冒号——:OLD 或 :NEW
这意味着如果你想访问比如说old.id_ruang
,你应该像这样访问它::old.id_ruang
综上所述,代码应如下所示:
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;
推荐阅读
- typescript - plugins.legend.align 是不兼容的类型 typescript chartjs
- ajax - 如果我们要使用ajax,我们还需要使用guzzle吗?
- service-worker - 未检测到匹配的服务人员 - Angular 9
- python - python将args附加到函数调用
- postgresql - visibility map: all visible pages
- github-actions - Reduce build time in GitOps by using Docker image layers from previous build with Azure Registry
- javascript - Edge Webview2 指定的 JS API 是什么?
- bluetooth - 是否有通过 BLE 发送 GPS 导航的标准方法?
- postman - 如何将请求参数存储在变量中并在另一个请求中使用它
- c# - 如何在 Wix Installer 中指定安装目录路径