The CREATE OR REPLACE the TRIGGER bi_film_desp
AFTER INSERT ON film
FOR EACH ROW
DECLARE
O_name VARCHAR2 (100);
N_name VARCHAR2 (100);
V_n NUMBER;
V_r VARCHAR2 (100);
V_l VARCHAR2 (100);
V_o_l VARCHAR2 (100);
The BEGIN

- 1. The rating, language_id, original_language_id in any item is null, the trigger should not be triggered
SELECT: new rating INTO v_r FROM dual;
SELECT: new language_id INTO v_l FROM dual;
SELECT: new original_language_id INTO v_o_l FROM dual;

IF v_r IS NULL OR v_l IS NULL OR v_o_l IS NULL THEN
Dbms_output. Put_line (' rating, language_id, original_language_id cannot be null).

The ELSE

-- 2. Meet the conditionsThe SELECT l.n ame INTO o_name FROM LANGUAGE l WHERE l.l anguage_id=v_o_l;
The SELECT l.n ame INTO n_name FROM LANGUAGE l WHERE l.l anguage_id=v_l;
- a. interpretation whether there is the same rating movies
IF v_r=: old. Rating THEN
SELECT COUNT (1) + 1 INTO v_n FROM film t WHERE t.r ating=v_r;
The UPDATE film t
The SET t.d escription=: new description | | rating | | '-' | | v_n | |
': Originally in' | | o_name | |
'Re - released in' | | n_name
WHERE t.f ilm_id=: new. Film_id;
- there is no repeat
The ELSE
The UPDATE film t

The SET t.d escription=: new description | | rating | | ': Originally in' | |
O_name | | 'Re - released in' | | n_name
WHERE t.f ilm_id=: new. Film_id;
END IF;
COMMIT;
END IF;
END;

CodePudding user response:

- Create table
The create table FILM
(
Film_id NUMBER (5) not null,
The title VARCHAR2 (255),
The description VARCHAR2 (255),
Release_year NUMBER (4),
Language_id NUMBER (3) not null,
Original_language_id NUMBER (3),
Rental_duration NUMBER (3) the default 3 not null,
Rental_rate NUMBER (4, 2) the default '4.99' not null,
Length NUMBER (5),
Replacement_cost NUMBER (5, 2) the default '19.99' not null,
The default rating VARCHAR2 (8) 'G' not null,
Special_features VARCHAR2 (255)
)

CodePudding user response:

Added a PRAGMA AUTONOMOUS_TRANSACTION; Commit, but did not trigger, unclear what effect is
The CREATE OR REPLACE the TRIGGER bi_film_desp
AFTER INSERT ON film
FOR EACH ROW
DECLARE
O_name VARCHAR2 (100);
N_name VARCHAR2 (100);
V_n NUMBER;
V_r VARCHAR2 (100);
V_l VARCHAR2 (100);
V_o_l VARCHAR2 (100);
PRAGMA AUTONOMOUS_TRANSACTION;
The BEGIN

- 1. The rating, language_id, original_language_id in any item is null, the trigger should not be triggered
SELECT: new rating INTO v_r FROM dual;
SELECT: new language_id INTO v_l FROM dual;
SELECT: new original_language_id INTO v_o_l FROM dual;

IF v_r IS NULL OR v_l IS NULL OR v_o_l IS NULL THEN
Dbms_output. Put_line (' rating, language_id, original_language_id cannot be null).

The ELSE

-- 2. Meet the conditionsThe SELECT l.n ame INTO o_name FROM LANGUAGE l WHERE l.l anguage_id=v_o_l;
The SELECT l.n ame INTO n_name FROM LANGUAGE l WHERE l.l anguage_id=v_l;
- a. interpretation whether there is the same rating movies
SELECT COUNT (1) INTO v_n FROM film t WHERE t.r ating=v_r;
IF v_n & gt;=2 THEN
The UPDATE film t
The SET t.d escription=: new description | | rating | | '-' | | v_n | |
': Originally in' | | o_name | |
'Re - released in' | | n_name
WHERE t.f ilm_id=: new. Film_id;
- there is no repeat
The ELSE
The UPDATE film t
The SET t.d escription=: new description | | rating | | ': Originally in' | |
O_name | | 'Re - released in' | | n_name
WHERE t.f ilm_id=: new. Film_id;
END IF;
COMMIT;
END IF;
END;