Help create a trigger for a non-updatable view.
The tables look like this.
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
);
The view itself looks like this
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;
I tried to create a trigger like this. But for some reason it is not created.( Error at line 86: PL/SQL: SQL Statement ignored Error at line 94: PL/SQL: ORA-00917: missing comma)
it seems to me that I went the hard way. help fix this code.
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;
/
Advertisement
Answer
You say that you’re getting an error creating the trigger. But if I try to replicate your problem, I can’t because the definition of the Software table is invalid. First, the foreign key references a table Manufacturer that doesn’t exist in your example. And second, the foreign key constraint definition has an extra comma at the end.
If I remove the foreign key constraint entirely
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
);
That allows the script to get as far as trying to create the trigger. At that point, you’ll get errors which include (but are not limited to) the ORA-00917 error you mentioned.
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'
The PLS-00049: bad bind variable errors occur because there are no columns called expiration_date or total_cost in the definition of the view. Thus, there is no such thing as :new.expiration_date or :new.total_cost because DML against the view cannot specify values for those columns. I have no idea how you want to handle that– either omit the values, provide default values, read the values from somewhere else, add the columns to the view and provide them in the DML against the view, etc. The fact that expiration_date is defined as not null seemingly limits your options.
If you look at line 86 of your trigger, you’ll see this statement
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
);
which isn’t syntactically valid.
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
);
would be valid if :new.expiration_date and :new.total_cost were valid identifiers. But, as mentioned above, they’re not valid. And it isn’t obvious what you would want the values to be on insert.
Here is a liveSQL example that you can play with to make sure what you have is a reproducible test case.