Oracle PL/SQL: Compilation problems with triggers - ORA-00904 errors and invalid identifiers

Question

I'm making different constraints on my database for a project, but I have errors that I can't get out of and I just don't understand the meaning.

I work on SQL Developer with Oracle.

Here is my script to create my DB:

sql

-- Table Video
CREATE TABLE Video (
  ID INTEGER,
  title VARCHAR(200),
  description VARCHAR(500),
  main_theme VARCHAR(100),
  type VARCHAR2(50),
  link VARCHAR(500),
  duration VARCHAR(26),
  customer_rating DECIMAL(3,2),
  classification VARCHAR(50),
  country VARCHAR(50),
  year_production INTEGER,
  CONSTRAINT PK_Video PRIMARY KEY (ID)
);

-- Table Entertainment
CREATE TABLE Entertainment (
  ID INTEGER,
  video_ID INT,
  CONSTRAINT PK_Entertainment PRIMARY KEY (ID),
  CONSTRAINT FK_Entertainment_Video FOREIGN KEY (video_ID) REFERENCES Video(ID)
);

-- Table Children
CREATE TABLE Children (
  ID INTEGER,
  video_ID INT,
  CONSTRAINT PK_Children PRIMARY KEY (ID),
  CONSTRAINT FK_Children_Video FOREIGN KEY (video_ID) REFERENCES Video(ID)
);

-- Table Cinema
CREATE TABLE Cinema (
  ID INTEGER,
  video_ID INT,
  CONSTRAINT PK_Cinema PRIMARY KEY (ID),
  CONSTRAINT FK_Cinema_Video FOREIGN KEY (video_ID) REFERENCES Video(ID)
);

-- Table Episode
CREATE TABLE Episode (
  ID INTEGER,
  video_ID INT,
  name_episode VARCHAR(100),
  CONSTRAINT PK_Episode PRIMARY KEY (ID),
  CONSTRAINT FK_Episode_Video FOREIGN KEY (video_ID) REFERENCES Video(ID)
);

-- Table Format
CREATE TABLE Format (
  video_ID INTEGER,
  name VARCHAR(100),
  extension VARCHAR(50),
  CONSTRAINT PK_Format PRIMARY KEY (video_ID),
  CONSTRAINT FK_Format_Video FOREIGN KEY (video_ID) REFERENCES Video(ID)
);

-- Table Version
CREATE TABLE Version (
  ID INTEGER,
  video_ID INTEGER,
  price DECIMAL(4,2),
  quality VARCHAR(10),
  CONSTRAINT PK_Version PRIMARY KEY (ID),
  CONSTRAINT FK_Version_Video FOREIGN KEY (video_ID) REFERENCES Video(ID)
);

-- Table Software
CREATE TABLE Software (
  nom_software VARCHAR(100),
  CONSTRAINT PK_Software PRIMARY KEY (nom_software)
);

-- Table Extension
CREATE TABLE Extension (
  name VARCHAR(50),
  CONSTRAINT PK_Extension PRIMARY KEY (name) 
);

-- Table Keyword
CREATE TABLE Keyword (
  keyword VARCHAR(50),
  CONSTRAINT PK_Keyword PRIMARY KEY (keyword)
);

-- Table Type_Mime
CREATE TABLE Type_Mime (
  name VARCHAR(50),
  CONSTRAINT PK_Type_Mime PRIMARY KEY (name)
);

-- Table Customer
CREATE TABLE Customer (
  ID INTEGER,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  postal_address VARCHAR(100),
  email VARCHAR(100),
  phone_number VARCHAR(20),
  password VARCHAR(100),
  CONSTRAINT PK_Customer PRIMARY KEY (ID)
);

-- Table Type_Subscription
CREATE TABLE Type_Subscription (
  name VARCHAR(100),
  nb_cinema INTEGER,
  nb_serie INTEGER,
  nb_children INTEGER,
  nb_entertainment INTEGER,
  price DECIMAL(4,2),
  CONSTRAINT PK_Type_Subscription PRIMARY KEY (name)
);

-- Table Season
CREATE TABLE Season (
  ID INTEGER,
  description VARCHAR(500),
  CONSTRAINT PK_Season PRIMARY KEY (ID)
);

-- Table Serie
CREATE TABLE Serie (
  ID INTEGER,
  description VARCHAR(500),
  CONSTRAINT PK_Serie PRIMARY KEY (ID)
);

-- Table Worker
CREATE TABLE Worker (
  ID INTEGER,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  CONSTRAINT PK_Worker PRIMARY KEY (ID)
);

-- Table Video_Keyword
CREATE TABLE Video_Keyword (
  video_ID INTEGER,
  keyword VARCHAR(50),
  CONSTRAINT PK_Video_Keyword PRIMARY KEY (video_ID, keyword),
  CONSTRAINT FK_Video_Keyword_Video FOREIGN KEY (video_ID) REFERENCES Video(ID),
  CONSTRAINT FK_Video_Keyword_Keyword FOREIGN KEY (keyword) REFERENCES Keyword(keyword)
);

-- Table Cinema_Actor
CREATE TABLE Cinema_Actor (
  cinema_ID INTEGER,
  worker_ID INTEGER,
  CONSTRAINT PK_Cinema_Actor PRIMARY KEY (cinema_ID, worker_ID),
  CONSTRAINT FK_Cinema_Actor_Cinema FOREIGN KEY (cinema_ID) REFERENCES Cinema(ID),
  CONSTRAINT FK_Cinema_Actor_Worker FOREIGN KEY (worker_ID) REFERENCES Worker(ID)
);

-- Table Cinema_Scriptwriter
CREATE TABLE Cinema_Scriptwriter (
  cinema_ID INTEGER,
  worker_ID INTEGER,
  CONSTRAINT PK_Cinema_Scriptwriter PRIMARY KEY (cinema_ID, worker_ID),
  CONSTRAINT FK_Cinema_Scriptwriter_Cinema FOREIGN KEY (cinema_ID) REFERENCES Cinema(ID),
  CONSTRAINT FK_Cinema_Scriptwriter_Worker FOREIGN KEY (worker_ID) REFERENCES Worker(ID)
);

-- Table Children_Compositor
CREATE TABLE Children_Compositor (
  children_ID INTEGER,
  worker_ID INTEGER,
  CONSTRAINT PK_Children_Compositor PRIMARY KEY (children_ID, worker_ID),
  CONSTRAINT FK_Children_Compositor_Children FOREIGN KEY (children_ID) REFERENCES Children(ID),
  CONSTRAINT FK_Children_Compositor_Worker FOREIGN KEY (worker_ID) REFERENCES Worker(ID)
);

-- Table Children_Scriptwriter
CREATE TABLE Children_Scriptwriter (
  children_ID INTEGER,
  worker_ID INTEGER,
  CONSTRAINT PK_Children_Scriptwriter PRIMARY KEY (children_ID, worker_ID),
  CONSTRAINT FK_Children_Scriptwriter_Children FOREIGN KEY (children_ID) REFERENCES Children(ID),
  CONSTRAINT FK_Children_Scriptwriter_Worker FOREIGN KEY (worker_ID) REFERENCES Worker(ID)
);

-- Table Entertainment_Scriptwriter
CREATE TABLE Entertainment_Scriptwriter (
  entertainment_ID INTEGER,
  worker_ID INTEGER,
  CONSTRAINT PK_Entertainment_Scriptwriter PRIMARY KEY (entertainment_ID, worker_ID),
  CONSTRAINT FK_Entertainment_Scriptwriter_Entertainment FOREIGN KEY (entertainment_ID) REFERENCES Entertainment(ID),
  CONSTRAINT FK_Entertainment_Scriptwriter_Worker FOREIGN KEY (worker_ID) REFERENCES Worker(ID)
);

-- Table Entertainment_Stage_director
CREATE TABLE Entertainment_Stage_director (
  entertainment_ID INTEGER,
  worker_ID INTEGER,
  CONSTRAINT PK_Entertainment_Stage_director PRIMARY KEY (entertainment_ID, worker_ID),
  CONSTRAINT FK_Entertainment_Stage_director_Entertainment FOREIGN KEY (entertainment_ID) REFERENCES Entertainment(ID),
  CONSTRAINT FK_Entertainment_Stage_director_Worker FOREIGN KEY (worker_ID) REFERENCES Worker(ID)
);

-- Table Entertainment_Interpreter
CREATE TABLE Entertainment_Interpreter (
  entertainment_ID INTEGER,
  worker_ID INTEGER,
  CONSTRAINT PK_Entertainment_Interpreter PRIMARY KEY (entertainment_ID, worker_ID),
  CONSTRAINT FK_Entertainment_Interpreter_Entertainment FOREIGN KEY (entertainment_ID) REFERENCES Entertainment(ID),
  CONSTRAINT FK_Entertainment_Interpreter_Worker FOREIGN KEY (worker_ID) REFERENCES Worker(ID)
);

-- Table Season_Actor
CREATE TABLE Season_Actor (
  season_ID INTEGER,
  worker_ID INTEGER,
  CONSTRAINT PK_Season_Actor PRIMARY KEY (season_ID, worker_ID),
  CONSTRAINT FK_Season_Actor_Season FOREIGN KEY (season_ID) REFERENCES Season(ID),
  CONSTRAINT FK_Season_Actor_Worker FOREIGN KEY (worker_ID) REFERENCES Worker(ID)
);

-- Table Season_Producer
CREATE TABLE Season_Producer (
  season_ID INTEGER,
  worker_ID INTEGER,
  CONSTRAINT PK_Season_Producer PRIMARY KEY (season_ID, worker_ID),
  CONSTRAINT FK_Season_Producer_Season FOREIGN KEY (season_ID) REFERENCES Season(ID),
  CONSTRAINT FK_Season_Producer_Worker FOREIGN KEY (worker_ID) REFERENCES Worker(ID)
);

-- Table Buy
CREATE TABLE Buy (
  customer_ID INTEGER,
  version_ID INTEGER,
  rating DECIMAL(3, 2),
  CONSTRAINT PK_Buy PRIMARY KEY (customer_ID, version_ID),
  CONSTRAINT FK_Buy_Customer FOREIGN KEY (customer_ID) REFERENCES Customer(ID),
  CONSTRAINT FK_Buy_Version FOREIGN KEY (version_ID) REFERENCES Version(ID)
);

-- Table Rent
CREATE TABLE Rent (
  customer_ID INTEGER,
  version_ID INTEGER,
  start_date DATE,
  end_date DATE,
  rating DECIMAL(3, 2),
  CONSTRAINT PK_Rent PRIMARY KEY (customer_ID, version_ID),
  CONSTRAINT FK_Rent_Customer FOREIGN KEY (customer_ID) REFERENCES Customer(ID),
  CONSTRAINT FK_Rent_Version FOREIGN KEY (version_ID) REFERENCES Version(ID)
);

Here is also my entire script for different constraints that I have explained in comments to each one:

sql

-- Constraint 1: a link to a trailer must start with http
ALTER TABLE Video
ADD CONSTRAINT CK_Video_Link_HTTP CHECK (link LIKE 'http%');

-- Constraint 2: the evaluation on a content must be between 0 and 5
ALTER TABLE Video
ADD CONSTRAINT CK_Video_Rating_Between CHECK (customer_rating BETWEEN 0 AND 5);

-- Constraint 3: the year of production must be higher than 1900
ALTER TABLE Video
ADD CONSTRAINT CK_Video_Year_GT_1900 CHECK (year_production > 1900);

-- Constraint 4: passwords must be at least 8 characters long, contain letters and numbers, and include at least one capital letter and one special character
ALTER TABLE Customer
ADD CONSTRAINT CK_Customer_Password_Regex CHECK (REGEXP_LIKE(password, '(?=.*\d)(?=.*[a-z])(?=.*[A-Z])(?=.*[!@#$%^&*()])(.{8,})'));

-- Constraint 5: Youth content cannot be under 16 or under 18
CREATE OR REPLACE TRIGGER trg_children_classification
BEFORE INSERT OR UPDATE OF classification ON Video
FOR EACH ROW
WHEN (NEW.type = 'Children')
DECLARE
BEGIN
  IF :NEW.classification IN ('-16', '-18') THEN
    RAISE_APPLICATION_ERROR(-20003, 'Youth content cannot be under 16 or under 18');
  END IF;
END;
/

-- Constraint 6: the number of subscriber rentals must be less than or equal to that authorized by their subscriptions
CREATE OR REPLACE TRIGGER trg_rent_limit
BEFORE INSERT ON Rent
FOR EACH ROW
DECLARE
  v_rent_count NUMBER;
  v_max_rent_count NUMBER;
BEGIN
  SELECT COUNT(*)
  INTO v_rent_count
  FROM Rent
  WHERE customer_ID = :NEW.customer_ID;

  SELECT ts.nb_cinema + ts.nb_serie + ts.nb_children + ts.nb_entertainment
  INTO v_max_rent_count
  FROM Customer c
  JOIN Type_Subscription ts ON c.type_subscription = ts.ID
  WHERE c.ID = :NEW.customer_ID;

  IF v_rent_count >= v_max_rent_count THEN
    RAISE_APPLICATION_ERROR(-20002, 'Subscribers rentals must be less than or equal to the number authorized by their subscriptions');
  END IF;
END;
/

-- Constraint 7: a customer cannot have two subscriptions at the same time
CREATE OR REPLACE TRIGGER trg_customer_subscription
BEFORE INSERT ON Rent
FOR EACH ROW
DECLARE
  v_rent_duration NUMBER;
BEGIN
  SELECT ts.nb_days
  INTO v_rent_duration
  FROM Customer c
  JOIN Type_Subscription ts ON c.type_subscription_id = ts.ID
  WHERE c.ID = :NEW.customer_ID;

  :NEW.end_date := :NEW.start_date + v_rent_duration;
END;
/

-- Constraint 8: A subscriber cannot rent content if the rental date for that content has passed
CREATE OR REPLACE TRIGGER trg_rent_date
BEFORE INSERT ON Rent
FOR EACH ROW
DECLARE
  v_content_date DATE;
BEGIN
  SELECT r.end_date
  INTO v_content_date
  FROM Video v
  JOIN Rent r ON v.ID = r.video_ID
  WHERE r.ID = :NEW.version_ID;

  IF v_content_date > SYSDATE THEN
    RAISE_APPLICATION_ERROR(-20004, 'A subscriber cannot rent content if the rental date for that content has passed');
  END IF;
END;
/

And here are the errors that are displayed to me in Console.

Trigger element TRG_CHILDREN_CLASSIFICATION compiled


Trigger element TRG_RENT_LIMIT compiled

LINE/COL ERROR
--------- -------------------------------------------------------------
10/3 PL/SQL: SQL Statement ignored
13/54 PL/SQL: ORA-00904: "TS". "ID": invalid identifier
Errors: consult the compiler log

Trigger element TRG_CUSTOMER_SUBSCRIPTION compiled

LINE/COL ERROR
--------- -------------------------------------------------------------
4/3 PL/SQL: SQL Statement ignored
7/57 PL/SQL: ORA-00904: "TS". "ID": invalid identifier
Errors: consult the compiler log

Trigger element TRG_RENT_DATE compiled

LINE/COL ERROR
--------- -------------------------------------------------------------
4/3 PL/SQL: SQL Statement ignored
7/25 PL/SQL: ORA-00904: "R". "VIDEO_ID": invalid identifier
Errors: consult the compiler log

The newspaper displays 2 errors to me: "Error(105,3): PL/SQL: SQL Statement ignored" and "Error(108,25): PL/SQL: ORA-00904: "R". "VIDEO_ID": invalid identifier"

The first one points me to the line:

"IF :NEW.classification IN ('-16', '-18') THEN" from the children_classification trigger.

The second error simply points me to the END at the end of the Trigger.

I tried For the trg_rent_limit trigger to modify the problematic line as follows:

JOIN Type_Subscription ts ON c.type_subscription = ts.name

but replacing the name with the ts.ID.

I also tried to modify a lot of things on the trigger_rent_date (because I thought that the problem came from there with incorrect references) but still the same problem.

And I also have the impression that the problem is not with the children_classification but with the rent_date.

Answer 1

It is too much text to illustrate in a limited comment, so - here you are.

All tables and constraints up to this one are successfully created, but this one fails:

SQL> -- Constraint 6: the number of subscriber rentals must be less than or equal to that authorized by their subscriptions
SQL> CREATE OR REPLACE TRIGGER trg_rent_limit
  2  BEFORE INSERT ON Rent
  3  FOR EACH ROW
  4  DECLARE
  5    v_rent_count NUMBER;
  6    v_max_rent_count NUMBER;
  7  BEGIN
  8    SELECT COUNT(*)
  9    INTO v_rent_count
 10    FROM Rent
 11    WHERE customer_ID = :NEW.customer_ID;
 12
 13    SELECT ts.nb_cinema + ts.nb_serie + ts.nb_children + ts.nb_entertainment
 14    INTO v_max_rent_count
 15    FROM Customer c
 16    JOIN Type_Subscription ts ON c.type_subscription = ts.ID
 17    WHERE c.ID = :NEW.customer_ID;
 18
 19    IF v_rent_count >= v_max_rent_count THEN
 20      RAISE_APPLICATION_ERROR(-20002, 'Subscribers rentals must be less than or equal to the number authorized by their subscriptions');
 21    END IF;
 22  END;
 23  /

Warning: Trigger created with compilation errors.

SQL> show err
Errors for TRIGGER TRG_RENT_LIMIT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
10/3     PL/SQL: SQL Statement ignored
13/54    PL/SQL: ORA-00904: "TS"."ID": invalid identifier
SQL>

Query that failed is:

SQL> SELECT ts.nb_cinema + ts.nb_serie + ts.nb_children + ts.nb_entertainment
  2    FROM Customer c
  3    JOIN Type_Subscription ts ON c.type_subscription = ts.ID;
  JOIN Type_Subscription ts ON c.type_subscription = ts.ID
                                                     *
ERROR at line 3:
ORA-00904: "TS"."ID": invalid identifier


SQL>

It says that customer table should contain column named type_subscription; does it?

SQL> desc customer
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(38)
 FIRST_NAME                                         VARCHAR2(50)
 LAST_NAME                                          VARCHAR2(50)
 POSTAL_ADDRESS                                     VARCHAR2(100)
 EMAIL                                              VARCHAR2(100)
 PHONE_NUMBER                                       VARCHAR2(20)
 PASSWORD                                           VARCHAR2(100)

Nope, no type_subscription column here.

Error also says that type_subscription table should contain id column:

SQL> desc type_subscription
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                      NOT NULL VARCHAR2(100)
 NB_CINEMA                                          NUMBER(38)
 NB_SERIE                                           NUMBER(38)
 NB_CHILDREN                                        NUMBER(38)
 NB_ENTERTAINMENT                                   NUMBER(38)
 PRICE                                              NUMBER(4,2)

SQL>

Nope, no id column here either.

Therefore, how can you expect that query/trigger to work?

---

As I said: revise tables involved and fix what's missing.