幫助為不可更新的視圖創建觸發器。
桌子看起來像這樣。
CREATE TABLE Users(id_user INTEGER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1 NOCACHE) PRIMARY KEY,
surname VARCHAR2(30) NOT NULL,
name VARCHAR2(30) NOT NULL,
patronymic VARCHAR2(30) NULL,
tel_no VARCHAR2(17) NOT NULL,
CONSTRAINT ch_telno_users
CHECK(REGEXP_LIKE(tel_no, '^\ 375\(\d{2}\)\d{3}-\d{2}-\d{2}$')),
CONSTRAINT uni_telno_users
UNIQUE(tel_no)
);
CREATE TABLE Software(id_sw INTEGER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1 NOCACHE) PRIMARY KEY,
name_sw VARCHAR2(40) NOT NULL,
version VARCHAR2(10) NULL,
license_period VARCHAR2(10) NOT NULL,
id_mfr INTEGER NOT NULL,
release_date DATE NOT NULL,
price NUMBER(9,2),
total_amount INTEGER NOT NULL,
CONSTRAINT id_mfr_fk
FOREIGN KEY(id_mfr)
REFERENCES Manufacturer,
);
CREATE SYNONYM sw for Software;
CREATE TABLE Sales(id_sale INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
id_user INTEGER NOT NULL,
id_sw INTEGER NOT NULL,
quantity INTEGER NOT NULL,
total_cost NUMBER(9,2),
order_date DATE NOT NULL CHECK (order_date >= to_date('01/01/2019', 'dd/mm/yyyy')),
expiration_date DATE NOT NULL,
CONSTRAINT id_user_fk
FOREIGN KEY(id_user)
REFERENCES Users,
CONSTRAINT id_sw_fk
FOREIGN KEY(id_sw)
REFERENCES Software
);
視圖本身看起來像這樣
CREATE OR REPLACE VIEW SalesView AS
SELECT Sales.id_sale,sw.name_sw||' '||sw.version sw_full_name,
Users.surname||' '||Users.name||' '||Users.patronymic user_full_name,
sales.quantity, sales.order_date
FROM sw INNER JOIN (Users INNER JOIN Sales ON Sales.id_user = Users.id_user) ON sw.id_sw = sales.id_sw;
我試圖創建一個這樣的觸發器。但由于某種原因它沒有被創建。（第 86 行錯誤：PL/SQL：SQL 陳述句被忽略 第 94 行錯誤：PL/SQL：ORA-00917：缺少逗號）
在我看來，我走得很艱難。幫助修復此代碼。
CREATE OR REPLACE TRIGGER sales_view_instead_of_trig INSTEAD OF
UPDATE OR INSERT ON salesview
FOR EACH ROW
DECLARE
sw_new sw.id_sw%TYPE;
user_new Users2.id_user%TYPE;
check_excep_sw VARCHAR2(51);
check_excep_user VARCHAR2(92);
BEGIN
IF updating THEN --* update
IF :new.sw_full_name != :old.sw_full_name THEN
SELECT
sw.name_sw||' '||sw.version
INTO check_excep_sw
FROM
sw
WHERE
sw.name_sw||' '||sw.version = :new.sw_full_name;
SELECT
id_sw
INTO sw_new
FROM
sw
WHERE
sw.name_sw||' '||sw.version = :new.sw_full_name;
ELSE
SELECT
id_sw
INTO sw_new
FROM
sw
WHERE
sw.name_sw||' '||sw.version = :old.sw_full_name;
END IF;
IF :new.user_full_name != :old.user_full_name THEN
SELECT
Users2.surname||' '||Users2.name||' '||Users2.patronymic
INTO check_excep_user
FROM
Users2
WHERE
Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name;
SELECT
id_user
INTO user_new
FROM
Users2
WHERE
Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name;
ELSE
SELECT
id_user
INTO user_new
FROM
Users2
WHERE
Users2.surname||' '||Users2.name||' '||Users2.patronymic = :old.user_full_name;
END IF;
UPDATE sales
SET
id_user = user_new,
id_sw = sw_new,
quantity = :new.quantity,
order_date = :new.order_date,
expiration_date = :new.expiration_date,
total_cost = :new.total_cost
WHERE
id_sale = :old.id_sale;
END IF;
IF inserting THEN --* insert
SELECT sw.name_sw||' '||sw.version INTO check_excep_sw
FROM sw
WHERE sw.name_sw||' '||sw.version = :new.sw_full_name;
SELECT id_sw
INTO sw_new
FROM sw
WHERE sw.name_sw||' '||sw.version = :new.sw_full_name;
SELECT
Users2.surname||' '||Users2.name||' '||Users2.patronymic
INTO check_excep_user
FROM
Users2
WHERE
Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name;
SELECT id_user
INTO user_new
FROM Users2
WHERE Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name;
INSERT INTO sales(
id_user,
id_sw,
quantity,
order_date,
expiration_date,
total_cost
) VALUES (
id_user = user_new,
id_sw = sw_new,
quantity = :new.quantity,
order_date = :new.order_date,
expiration_date = :new.expiration_date,
total_cost = :new.total_cost
);
END IF;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('-------------------------------------------');
dbms_output.put_line('| ERROR! |');
dbms_output.put_line('-------------------------------------------');
END;
/
uj5u.com熱心網友回復：
你說你在創建觸發器時遇到錯誤。但是，如果我嘗試復制您的問題，則無法復制，因為Software表的定義無效。首先，外鍵參考了Manufacturer示例中不存在的表。其次，外鍵約束定義在末尾有一個額外的逗號。
如果我完全洗掉外鍵約束
CREATE TABLE Software(id_sw INTEGER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1 NOCACHE) PRIMARY KEY,
name_sw VARCHAR2(40) NOT NULL,
version VARCHAR2(10) NULL,
license_period VARCHAR2(10) NOT NULL,
id_mfr INTEGER NOT NULL,
release_date DATE NOT NULL,
price NUMBER(9,2),
total_amount INTEGER NOT NULL
);
這允許腳本盡可能地嘗試創建觸發器。此時，您將收到錯誤，其中包括（但不限于）您提到的 ORA-00917 錯誤。
Errors: TRIGGER SALES_VIEW_INSTEAD_OF_TRIG
Line/Col: 62/27 PLS-00049: bad bind variable 'NEW.EXPIRATION_DATE'
Line/Col: 63/22 PLS-00049: bad bind variable 'NEW.TOTAL_COST'
Line/Col: 86/5 PL/SQL: SQL Statement ignored
Line/Col: 94/17 PL/SQL: ORA-00917: missing comma
Line/Col: 98/27 PLS-00049: bad bind variable 'NEW.EXPIRATION_DATE'
Line/Col: 99/22 PLS-00049: bad bind variable 'NEW.TOTAL_COST'
在PLS-00049: bad bind variable因為沒有所謂的列發生錯誤expiration_date或total_cost視圖的定義。因此，沒有:new.expiration_date或:new.total_cost因為針對視圖的 DML 無法為這些列指定值。我不知道你想如何處理——要么省略值，提供默認值，從其他地方讀取值，將列??添加到視圖并在 DML 中針對視圖提供它們，等等。事實expiration_date是定義為not null看似限制了您的選擇。
如果您查看觸發器的第 86 行，您將看到此陳述句
INSERT INTO sales(
id_user,
id_sw,
quantity,
order_date,
expiration_date,
total_cost
) VALUES (
id_user = user_new,
id_sw = sw_new,
quantity = :new.quantity,
order_date = :new.order_date,
expiration_date = :new.expiration_date,
total_cost = :new.total_cost
);
這在語法上無效。
INSERT INTO sales(
id_user,
id_sw,
quantity,
order_date,
expiration_date,
total_cost
) VALUES (
user_new,
sw_new,
:new.quantity,
:new.order_date,
:new.expiration_date,
:new.total_cost
);
如果:new.expiration_date并且:new.total_cost是有效識別符號，則將是有效的。但是，如上所述，它們是無效的。您希望插入的值是什么并不明顯。
這是一個liveSQL 示例，您可以使用它來確保您擁有的是一個可重現的測驗用例。
uj5u.com熱心網友回復：
“INSERT INTO sales”陳述句的 VALUES 中缺少冒號：
id_user = :user_new, id_sw = :sw_new,
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/392592.html