sql - 触发 SQL 语句

Question

嗨，我正在编写一个语句来在 Oracle 中创建一个触发器，该触发器会将文本附加到插入数据库中的每个新游戏的描述中。

我希望格式像

Format: (rating). (Name) is (Genre)
Example: M18. Dragon Ball, Genre is Fighting/Adventure. 

Game Table 中的 GenreID 和 subGenre 是 Genre Table 的外键。

游戏ID序列

CREATE SEQUENCE "GAME_ID_SEQ" MINVALUE 100 MAXVALUE 999999999
INCREMENT BY 5 START WITH 100;

GameID 触发器

CREATE OR REPLACE TRIGGER "tr_gameID"
   BEFORE INSERT ON "GAME"
   FOR EACH ROW
BEGIN
   SELECT "GAME_ID_SEQ".NEXTVAL INTO :NEW.gameID FROM DUAL;
END;
/

游戏描述触发器

CREATE OR REPLACE TRIGGER "GAME_DES"
  BEFORE INSERT OF GAME
  FOR EACH ROW
DECLARE
gen VARCHAR2(8);
subGen VARCHAR2(8);
BEGIN
  SELECT name INTO gen FROM GENRE WHERE GenreID = :NEW.GenreID;
  SELECT name INTO subGen FROM GENRE WHERE subGenreID = :NEW.GenreID;
  SELECT CONCAT(rating,".", title ,"Genre is", gen, "/", subGen) INTO :NEW.description FROM DUAL;
END;
/ 

我不确定我在哪里做错了。但我不断收到“警告：使用编译错误创建的触发器”。

标签： sqloracletriggers

Answer 1

你做错了什么？几件事。

TR_GAMEID可以（虽然，可以改写为）

SQL> create or replace trigger tr_gameid
  2    before insert on game
  3    for each row
  4  begin
  5    :new.gameid := game_id_seq.nextval;
  6  end;
  7  /

Trigger created.

---

GAME_DES 不正常，出现各种错误。

• 不是before insert OF但ON
• 表中的第二个select参考subgenreid列genre，但是-根据您发布的内容-表中不存在这样的列（但存在于game）
• concat只允许 2 个参数。您宁愿切换到双管道||连接运算符。
  • 另外，您正在连接一些未知的rating事物title 。这些是什么？

以下触发器编译但可能是错误的，因为第二个select看起来很可疑。

SQL> create or replace trigger game_Des
  2    before insert on game
  3    for each row
  4  declare
  5    gen    varchar2(8);
  6    subgen varchar2(8);
  7  begin
  8    select name into gen    from genre where genreid = :new.genreid;
  9    select name into subgen from genre where genreid = :new.genreid;
 10    :new.description := 'Genre is ' || gen ||'/'|| subgen;
 11  end;
 12  /

Trigger created.

SQL>

---

此外，还有一条建议：当 Oracle 说您遇到错误时，请询问它们是哪些错误。如何？像这样（在 SQL*Plus 中）（这是您的代码）：

SQL> CREATE OR REPLACE TRIGGER "GAME_DES"
  2    BEFORE INSERT ON GAME                   --> I fixed this
  3    FOR EACH ROW
  4  DECLARE
  5  gen VARCHAR2(8);
  6  subGen VARCHAR2(8);
  7  BEGIN
  8    SELECT name INTO gen FROM GENRE WHERE GenreID = :NEW.GenreID;
  9    SELECT name INTO subGen FROM GENRE WHERE subGenreID = :NEW.GenreID;
 10    SELECT CONCAT(rating,".", title ,"Genre is", gen, "/", subGen) INTO :NEW.description FROM DUAL;
 11  END;
 12  /

Warning: Trigger created with compilation errors.

SQL> show err
Errors for TRIGGER "GAME_DES":

LINE/COL ERROR
-------- -----------------------------------------------------------------
7/3      PL/SQL: SQL Statement ignored
7/10     PL/SQL: ORA-00909: invalid number of arguments
SQL>

或者，查询USER_ERRORS：

SQL> select text, line, position
  2  from user_errors
  3  where name = 'GAME_DES';

TEXT                                                     LINE   POSITION
-------------------------------------------------- ---------- ----------
PL/SQL: ORA-00909: invalid number of arguments              7         10
PL/SQL: SQL Statement ignored                               7          3

SQL>