Trigger SQL statement

Question

Hi so I have am writing a statement to create a trigger in Oracle that would append the text to the description of every new game inserted into the database.

I want the format to be like

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

GenreID and subGenre in Game Table is foreign key to Genre Table.

GameID Sequence

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

GameID Trigger

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;
/

Game Description Trigger

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;
/ 

I'm not sure where I'm doing wrong. But I keep getting "Warning: Trigger created with compilation errors."

Answer 1

What did you do wrong? Several things.

TR_GAMEID is OK (although, could be rewritten as)

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 isn't OK, suffers from various errors.

• it isn't before insert OF but ON
• the 2nd select refrences subgenreid column from the genre table, but - according to what you posted - such a column doesn't exist in the table (but exists in game)
• concat allows only 2 parameters. You'd rather switch to double pipe || concatenation operator.
  • also, you're concatenating some rating and title things which are unknown. What are they?

The following trigger compiles but is probably wrong as the 2nd select looks suspicious.

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>

---

Also, a piece of advice: when Oracle says you got errors, ask it which ones they were. How? Like this (in SQL*Plus) (this is your code):

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>

Alternatively, query 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>