Oracle SQL Trigger: After Update, Insert Rows into Another Table

Question

I am trying to write a trigger on one table that is triggered when the quantity in stock = 0. It will then add a row to another table with the product_id and store_id to indicate that item needs to be evaluated for reorder. After over 4 hours of searching and trying, I made an account here. I'm a new user and this could be totally off.

This is my latest attempt:

CREATE OR REPLACE TRIGGER ORDERS_AFTER_UPDATE
AFTER UPDATE 
OF QUANTITY_STOCK 
ON INVENTORY 
FOR EACH ROW
WHEN (NEW.QUANTITY_STOCK - OLD.QUANTITY_STOCK = 0)
BEGIN
SELECT PRODUCT_ID, STORE_ID
INTO PURCHASE_NEW
FROM INVENTORY
WHERE QUANTITY_STOCK = 0;
END;

The relevant tables are INVENTORY, having PRODUCT_ID, STORE_ID, and QUANTITY_STOCK and PURCHASE_NEW, having PRODUCT_ID and STORE_ID. I want it to copy PRODUCT_ID and STORE_ID into the PURCHASE_NEW table from the INVENTORY table when the value of QUANTITY_STOCK = 0.

Errors I'm receiving are:

Error(8,1): PL/SQL: SQL Statement ignored
Error(9,6): PLS-00403: expression 'PURCHASE_NEW' cannot be used as an INTO-target of a SELECT/FETCH statement
Error(9,19): PL/SQL: ORA-00904: : invalid identifier

Thank you very much.

Answer 1

I'm not sure why you are checking new and old quantity_stock values; code you wrote will make trigger fire only when you update that value to what it was, because only then will their difference be zero. But, I hope you know what you're doing.

Sample data:

SQL> create table inventory (product_id number, store_id number, quantity_stock number);

Table created.

SQL> create table purchase_new (product_id number, store_id number);

Table created.

SQL> insert into inventory values (100, 1, 20);

1 row created.

SQL> insert into inventory values (200, 2, 50);

1 row created.

SQL>
SQL> create or replace trigger orders_after_update
  2    after update of quantity_stock
  3    on inventory
  4    for each row
  5    when (new.quantity_stock - old.quantity_stock = 0)
  6  begin
  7    insert into purchase_new (product_id, store_id)
  8      values (:new.product_id, :new.store_id);
  9  end;
 10  /

Trigger created.

Nothing will happen because old and new values aren't the same:

SQL> update inventory set quantity_stock = 15 where product_id = 100;

1 row updated.

SQL> select * From inventory;

PRODUCT_ID   STORE_ID QUANTITY_STOCK
---------- ---------- --------------
       100          1             15
       200          2             50

SQL> select * From purchase_new;

no rows selected

But, if they are equal, then trigger will do something:

SQL> update inventory set quantity_stock = 15 where product_id = 100;

1 row updated.

SQL> select * From inventory;

PRODUCT_ID   STORE_ID QUANTITY_STOCK
---------- ---------- --------------
       100          1             15
       200          2             50

SQL> select * From purchase_new;

PRODUCT_ID   STORE_ID
---------- ----------
       100          1

SQL>

If trigger line #5 was

when (new.quantity_stock = 0)

then it would make more sense (to me, at least).

Answer 2

You insert rows into a table with INSERT. You access the row's new values with :NEW. If you want to know whether the new quantity_stock amount is zero, check :new.quantity_stock = 0. In the trigger's WHEN clause the NEW must have no preceding colon, however.

CREATE OR REPLACE TRIGGER orders_after_update
AFTER UPDATE OF quantity_stock ON inventory 
FOR EACH ROW
WHEN (new.quantity_stock = 0)
BEGIN
  INSERT INTO purchase_new (product_id, store_id) VALUES (:new.product_id, :new.store_id);
END;