sql - Creating a trigger to adjust product stock - APEX ORACLE -

i'm trying create trigger on apex oracle when purchase goes through orderline table, quantity set when customer buys products taken off stock in products table.

create or replace trigger updatestock after delete or update of quantity.orderline on orderline each row begin    set product_stock.products = product_stock.products - quantity.orderline    product_id.orderline = product_id.products end; 

basically want trigger notice quantity in orderline , minus product stock. however, following error:

ora-01748: simple column names allowed here

could point me in right direction?

sql:

drop table admin cascade constraints ; drop table users cascade constraints ; drop table stalls cascade constraints ; drop table products cascade constraints ; drop table stallholder cascade constraints ; drop table customerorder cascade constraints; drop table orderline cascade constraints; drop table collection cascade constraints; drop sequence admin_id_seq; drop sequence user_id_seq; drop sequence stall_id_seq; drop sequence product_id_seq; drop sequence stallholder_id_seq; drop sequence customerorder_id_seq; drop sequence orderline_id_seq; drop sequence collection_id_seq; create sequence admin_id_seq start 1; create sequence user_id_seq start 1; create sequence stall_id_seq start 1; create sequence product_id_seq start 1; create sequence stallholder_id_seq start 1; create sequence customerorder_id_seq start 1; create sequence collection_id_seq start 1; create sequence orderline_id_seq start 1;  create table collection (     collection_id number(20) not null primary key,     status        varchar(10) not null,     time          number(4) not null);  create table customerorder (     order_id      number(15) not null primary key,     order_date    number(8) not null,     status        varchar2(10) not null,     collection_id number(20) not null);  create table stalls (     stall_id      number(15) not null primary key,     stall_name    varchar(25) not null,     stall_desc    varchar(100),     stall_type    varchar(25) not null,     stallholder_id  number(25) not null);  create table products (     product_id    number(15) not null primary key,     product_name  varchar(25) not null,     product_type  varchar(15) not null,     product_price number(6,2) not null,     product_stock number(3) not null,         stall_id      number(15) not null);   create table orderline (     orderline_id  number(15) not null primary key,     quantity      number(2) not null,     totalprice    decimal(19,4) not null,     order_id      number(15) not null,     product_id    number(15) not null);  create table users (     user_id      number(15) not null primary key,     username    varchar2(25) not null,     password    varchar2(25) not null,     name        varchar2(25) not null,     surname     varchar2(25) not null,     address     varchar2(100) not null,     contactno    number(11) not null);    create table stallholder (     stallholder_id     number(25) not null primary key,     username           varchar2(25) not null,     password           varchar2(25) not null,     name               varchar2(25) not null,     surname            varchar2(25) not null,     contactno          number(11) not null);  create table admin (     admin_id      number(15) not null primary key,     username    varchar2(25) not null,     password    varchar2(25) not null);    insert admin values (admin_id_seq.nextval, 'test', 'test'); insert admin values (admin_id_seq.nextval, 'admin', 'admin');  insert stallholder values (stallholder_id_seq.nextval, 'stallholder1', 'stallholder1', 'stall 1', 'stall 1', '0'); insert stallholder values (stallholder_id_seq.nextval, 'stallholder2', 'stallholder2', 'stall 2', 'stall 2', '0'); insert stallholder values (stallholder_id_seq.nextval, 'stallholder3', 'stallholder3', 'stall 3', 'stall 3', '0'); insert stallholder values (stallholder_id_seq.nextval, 'stallholder4', 'stallholder4', 'stall 4', 'stall 4', '0'); insert stallholder values (stallholder_id_seq.nextval, 'stallholder5', 'stallholder5', 'stall 5', 'stall 5', '0'); insert stallholder values (stallholder_id_seq.nextval, 'stallholder6', 'stallholder6', 'stall 6', 'stall 6', '0');  insert users values (user_id_seq.nextval, 'test', 'test', 'test', 'test', 'test', '0'); insert users values (user_id_seq.nextval, 'test2', 'test2', 'test2', 'test2', 'test2', '0'); insert users values (user_id_seq.nextval, 'test3', 'test3', 'test3', 'test3', 'test3', '0'); insert users values (user_id_seq.nextval, 'test4', 'test4', 'test4', 'test4', 'test4', '0');  insert stalls values (stall_id_seq.nextval, 'meat store', '', 'meat', '1'); insert stalls values (stall_id_seq.nextval, 'meat store 2', '', 'meat', '2'); insert stalls values (stall_id_seq.nextval, 'confectionary store', '', 'confectionary', '3'); insert stalls values (stall_id_seq.nextval, 'confectionary store 2', '', 'confectionary', '4'); insert stalls values (stall_id_seq.nextval, 'clothing store', '', 'clothing', '5'); insert stalls values (stall_id_seq.nextval, 'phone store', '', 'clothing', '6');  insert products values (product_id_seq.nextval, 'steak', 'meat', '5.99', '15', '0', '1'); insert products values (product_id_seq.nextval, 'steak', 'meat', '9.99', '10','0', '1'); insert products values (product_id_seq.nextval, 'steak', 'meat', '12.99', '10','0', '1'); insert products values (product_id_seq.nextval, 'pork', 'meat', '3.99', '20','0', '1'); insert products values (product_id_seq.nextval, 'pork', 'meat', '5.99', '20','0', '1'); insert products values (product_id_seq.nextval, 'pork', 'meat', '8.99', '15','0', '1'); insert products values (product_id_seq.nextval, 'bacon', 'meat', '1.99', '20','0', '1'); insert products values (product_id_seq.nextval, 'bacon', 'meat', '2.99', '20','0', '1'); insert products values (product_id_seq.nextval, 'bacon', 'meat', '3.99', '20','0', '1'); insert products values (product_id_seq.nextval, 'steak', 'meat', '5.99', '15','0', '2'); insert products values (product_id_seq.nextval, 'steak', 'meat', '9.99', '20','0', '2'); insert products values (product_id_seq.nextval, 'steak', 'meat', '12.99', '20','0', '2'); insert products values (product_id_seq.nextval, 'pork', 'meat', '3.99', '15','0', '2'); insert products values (product_id_seq.nextval, 'pork', 'meat', '5.99', '15','0', '2'); insert products values (product_id_seq.nextval, 'pork', 'meat', '8.99', '15','0', '2'); insert products values (product_id_seq.nextval, 'bacon', 'meat', '1.99', '20','0', '2'); insert products values (product_id_seq.nextval, 'bacon', 'meat', '2.99', '20','0', '2'); insert products values (product_id_seq.nextval, 'bacon', 'meat', '3.99', '20','0', '2'); insert products values (product_id_seq.nextval, 'haribo', 'sweets', '1.50', '50','0', '3'); insert products values (product_id_seq.nextval, 'haribo', 'sweets', '1.00', '50','0', '3'); insert products values (product_id_seq.nextval, 'picknmix100g', 'sweets', '2.00', '999','0', '3'); insert products values (product_id_seq.nextval, 'picknmix200g', 'sweets', '3.50', '999','0', '3'); insert products values (product_id_seq.nextval, 'toxicwaste', 'sweets','1.50','50','0','3'); insert products values (product_id_seq.nextval, 'haribo', 'sweets', '1.50', '100','0', '4'); insert products values (product_id_seq.nextval, 'haribo', 'sweets', '1.00', '100','0', '4'); insert products values (product_id_seq.nextval, 'picknmix100g', 'sweets', '2.00', '999','0', '4'); insert products values (product_id_seq.nextval, 'picknmix200g', 'sweets', '3.50', '999','0', '4'); insert products values (product_id_seq.nextval, 'toxicwaste', 'sweets', '1.50', '50','0', '4'); insert products values (product_id_seq.nextval, 't-shirts', 'clothing', '10.00', '20','0', '5'); insert products values (product_id_seq.nextval, 'shorts', 'clothing', '15.00', '20','0', '5'); insert products values (product_id_seq.nextval, 'jeans', 'clothing', '20.00', '20','0', '5'); insert products values (product_id_seq.nextval, 'hoodies', 'clothing', '20.00', '20','0', '5'); insert products values (product_id_seq.nextval, 'vests', 'clothing', '10.00', '20','0', '5');  alter table stalls add constraint fk_stallholder_id foreign key (stallholder_id) references stallholder(stallholder_id); alter table orderline add constraint fk_order_id foreign key (order_id) references customerorder(order_id); alter table orderline add constraint fk_product_id foreign key (product_id) references products(product_id); alter table customerorder add constraint fk_collection_id foreign key (collection_id) references collection(collection_id); alter table products add constraint fk_stall_id foreign key (stall_id) references stalls(stall_id); 

​

the error presumably coming of quantity.orderline because you're using table.column pattern rather column name. seem consistently putting table , column names wrong way around, , you're missing update before set, , trying refer columns in table row you're updating rather using new pseudorow. should closer:

create or replace trigger updatestock after delete or update of quantity on orderline each row begin    update products    set product_stock = product_stock - :new.quantity    product_id = :new.product_id end; 

but new values null on delete; aren't doing adjustment in insert; , on update maybe want taking both old , new quantity account.

even trying maintain running total in table works in single-user system, or updates serialised. if 2 people update same orderline row @ same time, trigger apply both updates products table, second block until first has committed, , applied old value @ start of statement - you'll lose first update, effectively.