Show film description with string format-CodePudding

I have a task need to write a trigger statement with format

This is my database schema:

I write my code like

CREATE OR REPLACE TRIGGER "BI_FILM_DESP"
BEFORE INSERT ON "FILM"
FOR EACH ROW
DECLARE
RatingFilm VARCHAR2(8);
Seq NUMBER(3);
OriginalL VARCHAR2(20);
Language VARCHAR2(20);
BEGIN
SELECT RATING INTO RatingFilm FROM FILM F;
SELECT COUNT(RATING) INTO Seq FROM FILM F GROUP BY F.RATING;
SELECT LANGUAGE.NAME INTO OriginalL FROM LANGUAGE L WHERE (L.LANGUAGE_ID =: FILM.LANGUAGE_ID);
SELECT LANGUAGE.NAME INTO Language FRoM LANGUAGE L WHERE (L.LANGUAGE_ID =: FILM.LANGUAGE_ID);
SELECT CONCAT(RatingFilm, "-", Seq, ": Originally in", OriginalL, ". Re-released in ", Language, ".");
END;
/

However it shows error

I think it's hard to read those errors and I need some help to correct it. Thanks in advance.

Edit: Add code that create tables

CREATE TABLE film (
  film_id NUMBER(5) NOT NULL,
  title varchar2(255),
  description varchar2(255),
  release_year NUMBER(4) DEFAULT NULL,
  language_id NUMBER(3) NOT NULL,
  original_language_id NUMBER(3) DEFAULT NULL,
  rental_duration NUMBER(3) DEFAULT 3 NOT NULL,
  rental_rate NUMBER(4,2) DEFAULT '4.99' NOT NULL,
  length NUMBER(5) DEFAULT NULL,
  replacement_cost NUMBER(5,2) DEFAULT '19.99' NOT NULL,
  rating varchar2(8) DEFAULT 'G' NOT NULL,
  special_features varchar2(255) DEFAULT NULL
);

CREATE TABLE language (
  language_id NUMBER(3) NOT NULL,
  name varchar2(20)
);

CodePudding user response：

I hope you read astentx's comment.

Here's how you could/should do it.

Tables involved (with necessary columns only):

SQL> CREATE TABLE language
  2  (
  3     language_id   NUMBER PRIMARY KEY,
  4     name          VARCHAR2 (20)
  5  );

Table created.

SQL> INSERT INTO language (language_id, name)
  2     SELECT 1, 'English' FROM DUAL
  3     UNION ALL
  4     SELECT 2, 'Croatian' FROM DUAL;

2 rows created.

SQL> CREATE TABLE film
  2  (
  3     film_id                NUMBER PRIMARY KEY,
  4     title                  VARCHAR2 (20),
  5     description            VARCHAR2 (100),
  6     language_id            NUMBER REFERENCES language,
  7     original_language_id   NUMBER REFERENCES language,
  8     rating                 NUMBER
  9  );

Table created.

Trigger: don't select from table on which that row-level trigger fires as you'd get mutating table error. Good for you, you don't have to do that - use :new pseudorecord values instead:

SQL> CREATE OR REPLACE TRIGGER bi_film_desp
  2     BEFORE INSERT
  3     ON film
  4     FOR EACH ROW
  5  DECLARE
  6     l_language           language.name%TYPE;
  7     l_original_language  language.name%TYPE;
  8  BEGIN
  9     SELECT l.name
 10       INTO l_language
 11       FROM language l
 12      WHERE l.language_id = :new.language_id;
 13
 14     SELECT l.name
 15       INTO l_original_language
 16       FROM language l
 17      WHERE l.language_id = :new.original_language_id;
 18
 19     :new.description :=
 20           'Rating: '
 21        || :new.rating
 22        || ', original language: '
 23        || l_original_language
 24        || ', language: '
 25        || l_language;
 26  END;
 27  /

Trigger created.

SQL>

Let's test it:

SQL> INSERT INTO film (film_id,
  2                    title,
  3                    language_id,
  4                    original_language_id,
  5                    rating)
  6       VALUES (1,
  7               'Izbavitelj',
  8               1,
  9               2,
 10               7);

1 row created.

Result:

SQL> select title, description, rating from film;

TITLE      DESCRIPTION                                                      RATING
---------- ------------------------------------------------------------ ----------
Izbavitelj Rating: 7, original language: Croatian, language: English             7

SQL>

Looks OK to me.