CREATE OR REPLACE 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);
BEGIN
--1. rating, language_id, original_language_id 中任意一項為 null，觸發器不應被觸發
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 不能為空');
ELSE
--2.滿足條件
SELECT l.name INTO o_name FROM LANGUAGE l WHERE l.language_id = v_o_l;
SELECT l.name INTO n_name FROM LANGUAGE l WHERE l.language_id = v_l;
--a.判讀是否存在相同的rating的電影
IF v_r = :old.rating THEN
SELECT COUNT(1) + 1 INTO v_n FROM film t WHERE t.rating = v_r;
UPDATE film t
SET t.description = :new.description || rating || '-' || v_n ||
': Originally in' || o_name ||
'. Re-released in' || n_name
WHERE t.film_id = :new.film_id;
--不存在重復
ELSE
UPDATE film t
SET t.description = :new.description || rating || ': Originally in' ||
o_name || '. Re-released in' || n_name
WHERE t.film_id = :new.film_id;
END IF;
COMMIT;
END IF;
END;
uj5u.com熱心網友回復：
-- Create tablecreate table FILM
(
film_id NUMBER(5) not null,
title VARCHAR2(255),
description VARCHAR2(255),
release_year NUMBER(4),
language_id NUMBER(3) not null,
original_language_id NUMBER(3),
rental_duration NUMBER(3) default 3 not null,
rental_rate NUMBER(4,2) default '4.99' not null,
length NUMBER(5),
replacement_cost NUMBER(5,2) default '19.99' not null,
rating VARCHAR2(8) default 'G' not null,
special_features VARCHAR2(255)
)
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/194502.html
標籤：開發