错误ORA-00922:触发器上缺少或无效操作

Question

我试图在从注册中删除某些内容时设置一个简单的触发因素，它也从分数和学生表中删除。我觉得我已经正确设置了所有内容，但是我一直遇到此错误：错误ORA-00922：缺少或无效的操作。我已经对这个错误进行了一些研究，但我没有到达任何地方。我正在SQL开发人员中这样做。我是SQL的新手，因此任何帮助将不胜感激。我的代码如下：

delimiter //
create or replace TRIGGER enrolls_trigger
AFTER DELETE ON ENROLLS
FOR EACH ROW
BEGIN
DELETE FROM scores
WHERE scores.sid= old.sid
AND scores.term = old.term AND scores.lineno = old.lineno
AND scores.compname = old.compname AND scores.points = old.points;
DELETE FROM students
WHERE students.sid = old.sid;
END//

Answer 1

您的语法有点关闭。要引用DML事务的先前值，您使用：旧。

-- delimiter
CREATE OR REPLACE TRIGGER enrolls_trigger AFTER
    DELETE ON enrolls
    FOR EACH ROW
BEGIN
    DELETE FROM scores
    WHERE
        scores.sid =:old.sid
        AND scores.term =:old.term
        AND scores.lineno =:old.lineno
        AND scores.compname =:old.compname
        AND scores.points =:old.points;
    DELETE FROM students
    WHERE
        students.sid =:old.sid;
END;
--delimiter

另外，如果您使用的是SQL开发人员，则需要拥有一个'''在触发主体上的终点关键字之后。

触发文档

为了更好地帮助您，请包括您的表DDL。我创建了这些以易于使用。

CREATE TABLE enrolls (
    sid        INTEGER,
    term       INTEGER,
    lineno     INTEGER,
    compname   VARCHAR2(10),
    points     INTEGER
);
CREATE TABLE scores (
    sid        INTEGER,
    term       INTEGER,
    compname   VARCHAR2(10),
    points     INTEGER,
    lineno     INTEGER
);
CREATE TABLE students (
    sid   INTEGER
);

Answer 2

我建议另一种方法 - 使用ON DELETE CASCADE选项创建的外键约束。看看以下示例。

感谢Jeff提供了我对我进行了稍作修改的创建表语句的感谢，即仅添加了必要的约束。请注意，我仅选择SID列是来自得分表的外键。顺便说一句，重复分数表中的许多列有什么意义？我建议您仅保留外键约束列(例如SID(，然后省略其他键。

最后，我们去这里：创建表：

SQL> CREATE TABLE enrolls (
  2      sid        INTEGER constraint pk_en primary key,
  3      term       INTEGER,
  4      lineno     INTEGER,
  5      compname   VARCHAR2(10),
  6      points     INTEGER
  7  );
Table created.
SQL> CREATE TABLE scores (
  2      sid        INTEGER,
  3      term       INTEGER,
  4      lineno     INTEGER,
  5      compname   VARCHAR2(10),
  6      points     INTEGER,
  7      constraint fk_sco_en foreign key (sid)
  8        references enrolls
  9        on delete cascade
 10  );
Table created.
SQL> CREATE TABLE students (
  2      sid   INTEGER,
  3      constraint fk_stu_en foreign key (sid)
  4        references enrolls
  5        on delete cascade
  6  );
Table created.
SQL>

插入样本数据：

SQL> insert into enrolls  (sid, term) values (100, 1);
1 row created.
SQL> insert into enrolls  (sid, term) values (200, 2);
1 row created.
SQL> insert into scores   (sid, term) values (100, 1);
1 row created.
SQL> insert into scores   (sid, term) values (200, 2);
1 row created.
SQL> insert into students (sid)       values (100);
1 row created.
SQL> insert into students (sid)       values (200);
1 row created.
SQL>
SQL> select * From students;
       SID
----------
       100
       200
SQL> select * From scores;
       SID       TERM     LINENO COMPNAME       POINTS
---------- ---------- ---------- ---------- ----------
       100          1
       200          2
SQL> select * From enrolls;
       SID       TERM     LINENO COMPNAME       POINTS
---------- ---------- ---------- ---------- ----------
       100          1
       200          2
SQL>

现在：如果我从注册表中删除一行，oracle将完成其余的工作：

SQL> delete from enrolls where sid = 100;
1 row deleted.
SQL> select * From students;
       SID
----------
       200
SQL> select * From scores;
       SID       TERM     LINENO COMPNAME       POINTS
---------- ---------- ---------- ---------- ----------
       200          2
SQL> select * From enrolls;
       SID       TERM     LINENO COMPNAME       POINTS
---------- ---------- ---------- ---------- ----------
       200          2
SQL>

看吗？sid = 100已从>所有表中删除，而没有任何其他编码(即无需触发(。