SQL trigger, that should be named BI_FILM_LANG, to append text to the description of every new film
Question:
SQL trigger, that should be named BI_FILM_LANG, to append text to the description of every new film inserted into the database. It is based on the language (language_id) and the original language (original_language_id) of the film. The format of the text you append should be (replacing tokens): Originally in . Re-released in . Original language and language should be the name of the language from the language table.
For example: If the following query was run: INSERT INTO FILM (title, description, language_id, original_language_id ) VALUES ('B Movie', 'Movie about wasps.', 1, 2); It should produce the following when the following select statement is run (based on the script file provided to you and assuming B Movie's id is 9999999): SQL> SELECT description FROM FILM WHERE film_id = 9999999; description--- Movie about wasps.Originally in Italian. Re-released in English. I have genrated this sql statement but its showing errors
CREATE OR REPLACE TRIGGER "BI_FILM_LANG"
BEFORE INSERT ON "FILM"
FOR EACH ROW
BEGIN
SELECT (CONCAT(OLD.DESCRIPTION,'.Originally in ',
(TO_CHAR(SELECT LANGUAGE.NAME FROM LANGUAGE , FILM
WHERE LANGUAGE.LANGUAGE_ID = FILM.LANGUAGE_ID)),
'.Re-released in ',
(TO_CHAR(SELECT LANGUAGE.NAME FROM LANGUAGE , FILM
WHERE LANGUAGE.LANGUAGE_ID = FILM.ORIGINAL_LANGUAGE_ID))))
INTO :NEW.DESCRIPTION FROM DUAL;
END;
/
Organizational Behaviour People Process Work And Human Resource Management
ISBN: 9780749463601
1st Edition
Authors: Stephen J Perkins, Raisa Arvinen-Muondo