我有以下表格：
CREATE TABLE Categorie
(categ_id NUMBER(5,0),
tip VARCHAR2(20));
CREATE TABLE Reteta
(reteta_id NUMBER(3),
nume VARCHAR2(100),
descriere VARCHAR2(200),
categ_id NUMBER(3,0),
vegetariana VARCHAR2(1) CHECK (vegetariana IN ('D','N')),
timp_preparare NUMBER(20,1),
portii NUMBER(3,1));
CREATE TABLE Set_ingrediente
(reteta_id NUMBER(3,0),
ingred_id NUMBER(3,0),
cantitate NUMBER(5,2),
um VARCHAR2(10) NOT NULL CHECK (um IN ('gr','ml','buc','lingurita','cana')),
comentarii VARCHAR2(100)
);
CREATE TABLE Ingredient
(ingred_id NUMBER(3,0),
ingredient VARCHAR2(30));
以及以下主鍵/外鍵：
ALTER TABLE Categorie
MODIFY (categ_id CONSTRAINT categ_id_pk PRIMARY KEY NOT NULL);
ALTER TABLE Reteta
MODIFY(CONSTRAINT reteta_id_pk PRIMARY KEY (reteta_id));
ALTER TABLE Ingredient
MODIFY(CONSTRAINT ingred_id_pk PRIMARY KEY (ingred_id));
ALTER TABLE Set_ingrediente
MODIFY( CONSTRAINT reteta_id_fk FOREIGN KEY (reteta_id) REFERENCES Reteta(reteta_id) ON DELETE CASCADE);
ALTER TABLE Set_ingrediente
MODIFY( CONSTRAINT ingred_id_fk FOREIGN KEY (ingred_id) REFERENCES Ingredient(ingred_id) ON DELETE CASCADE);
我必須寫一個觸發器：如果食譜使用成分“油”，請確保素食欄不能更改。這是我嘗試過的觸發器，但它不起作用：
CREATE OR REPLACE TRIGGER tr_oil BEFORE UPDATE
ON Reteta
FOR EACH ROW BEGIN
DECLARE
ingredient_var ingredient.ingredient%type;
SELECT ingredient
INTO ingredient_var
FROM Ingredint
WHERE ingred_id=
(SELECT ingred_id
FROM Set_ingrediente
WHERE reteta_id=NEW.reteta_id);
IF ingredient_var='oil' THEN
SET NEW.vegetariana = OLD.vegetariana;
END IF;
END;
uj5u.com熱心網友回復：
這太奇怪了；直到觸發代碼，您發布的所有內容都運行良好，所以我無法想象當涉及到充滿錯誤的觸發器時出了什么問題（錯位begin，無效的表名，缺少冒號和:new偽記錄，......）。好像兩個不同的人寫了你發布的兩段代碼。
反正：
SQL> CREATE OR REPLACE TRIGGER tr_oil
2 BEFORE UPDATE ON Reteta
3 FOR EACH ROW
4 DECLARE
5 ingredient_var ingredient.ingredient%type;
6 BEGIN
7 SELECT ingredient
8 INTO ingredient_var
9 FROM Ingredient
10 WHERE ingred_id = (SELECT ingred_id
11 FROM Set_ingrediente
12 WHERE reteta_id = :NEW.reteta_id);
13
14 IF ingredient_var='oil' THEN
15 :NEW.vegetariana := :OLD.vegetariana;
16 END IF;
17 END;
18 /
Trigger created.
SQL>
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/373090.html