Oracle 9i 自动增量触发器/序列不起作用

Question

在 Oracle 9i 服务器上执行以下 SQL 时，会产生错误“ORA-04098：触发器 'DBO.WTF_TRIGGER' 无效且重新验证失败”。

DROP TABLE "DBO".WTF;

CREATE TABLE "DBO".WTF
(id NUMBER PRIMARY KEY,
name VARCHAR2(30));

CREATE SEQUENCE "DBO".WTF_sequence
START WITH 1
INCREMENT BY 1;

CREATE OR REPLACE TRIGGER "DBO".WTF_trigger
BEFORE INSERT
ON "DBO".WTF
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT "DBO".WTF_sequence.nextval INTO :NEW.id FROM dual;
END;

INSERT INTO "DBO".WTF (name) VALUES ('asd');

有什么想法吗？

Answer 1

正如 APC 指出的那样，在 SQL*Plus 中执行 SHOW ERRORS 来打印错误会很有帮助。如果我创建一个具有适当权限的 DBO 用户，您发布的代码非常适合我。

SQL> conn / as sysdba
Connected.
SQL> create user dbo identified by dbo;

User created.

SQL> grant connect, resource, unlimited tablespace to dbo;

Grant succeeded.

SQL> conn dbo/dbo
Connected.
SQL> DROP TABLE "DBO".WTF;
DROP TABLE "DBO".WTF
                 *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> CREATE TABLE "DBO".WTF
  2  (id NUMBER PRIMARY KEY,
  3  name VARCHAR2(30));

Table created.

SQL>
SQL> CREATE SEQUENCE "DBO".WTF_sequence
  2  START WITH 1
  3  INCREMENT BY 1;

Sequence created.

SQL>
SQL> CREATE OR REPLACE TRIGGER "DBO".WTF_trigger
  2  BEFORE INSERT
  3  ON "DBO".WTF
  4  REFERENCING NEW AS NEW
  5  FOR EACH ROW
  6  BEGIN
  7  SELECT "DBO".WTF_sequence.nextval INTO :NEW.id FROM dual;
  8  END;
  9  /

Trigger created.

SQL> INSERT INTO "DBO".WTF (name) VALUES ('asd');

1 row created.

SQL> select * from wtf;

        ID NAME
---------- ------------------------------
         1 asd