SQL: Trigger Syntax

Question

I am doing some coursework where I am supposed to create a trigger that checks the stock level before an order is created, to see if there is enough product in stock to meet the order. Problem is the lecturer has not properly taught us the syntax of creating triggers, my issue being I dont know how to tell the trigger which row/collumn in the stocktable I am checking against. do I need to join the tables?

This is the code I have so far (I am just winging it here, it doesnt really work, but I'm putting it here to give you an idea of what it is I want to acchieve):

CREATE OR REPLACE TRIGGER stock_check
BEFORE INSERT OR UPDATE OF product_quantity ON orderline
FOR EACH ROW
DECLARE
Newtotal number;
insufficient_stock exception;
BEGIN
IF(:NEW.product_quantity > product_stock.stock_quantity) then
raise insufficient_stock;
Else
:new.product_stock.stock_quantity - product_quantity = newtotal
Update stock_quantity
Set product_quantity = newtotal


END IF;
exception
when insufficient_stock then
raise_application_error(-20604,'There is not enoguh stock available');
END;

Can you please define "doesn't really work" better? Are you getting any errors? Is it not doing what you expect? What is it doing and what do you expect? — Ben
– Ben, Commented Dec 10, 2012 at 15:25
This is all documented in the manual - especially the question about "which row you are working with"): docs.oracle.com/cd/E11882_01/appdev.112/e25519/… — user330315
– user330315, Commented Dec 10, 2012 at 15:27
And this - docs.oracle.com/cd/B19306_01/appdev.102/b14251/… — Anjan Biswas
– Anjan Biswas, Commented Dec 10, 2012 at 15:43

wolφi · Accepted Answer · 2012-12-10 15:48:42Z

1

I guess you'll need something like the following:

CREATE OR REPLACE TRIGGER stock_check
  BEFORE INSERT ON orderline
  FOR EACH ROW
DECLARE
  oldtotal NUMBER
  newtotal NUMBER;
  insufficient_stock EXCEPTION;
BEGIN
  SELECT stock_quantity INTO old_total
    FROM product_stock
   WHERE product_id = :new.product_id:

  IF(:new.product_quantity > oldtotal) then
     RAISE insufficient_stock;
  ELSE 
    newtotal := oldtotal - :new.product_quantity;        
    UPDATE stock_quantity
       SET product_quantity = newtotal
     WHERE product_id = :new.product_id;
  END IF;
EXCEPTION
  WHEN insufficient_stock THEN
    RAISE_APPLICATION_ERROR(-20604,'There is not enoguh stock available');
END;

You still have to add a column for some sort of product_id. And I didn't handle the UPDATE case...

answered Dec 10, 2012 at 15:48

wolφi

8,3814 gold badges42 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DazzaL · Accepted Answer · 2012-12-10 15:55:22Z

1

for example:

SQL> create table product_stock (prod_id number, stock_quantity number);

Table created.

SQL> create table orderline (prod_id number, product_quantity number);

Table created.

SQL> insert into product_stock values (1, 1002);

1 row created.

SQL>
SQL> CREATE OR REPLACE TRIGGER stock_check
  2  BEFORE INSERT OR UPDATE OF product_quantity ON orderline
  3  FOR EACH ROW
  4  DECLARE
  5    v_stock_quantity product_stock.prod_id%type;
  6    v_Newtotal number;
  7    insufficient_stock exception;
  8  BEGIN
  9    if updating
 10     then
 11       v_Newtotal := :new.product_quantity - :old.product_quantity;
 12     else
 13             v_Newtotal := :new.product_quantity;
 14     end if;
 15
 16      Update product_stock
 17      Set stock_quantity = stock_quantity - v_Newtotal
 18             where prod_id = :new.prod_id
 19             returning stock_quantity into v_stock_quantity;
 20
 21    IF (v_stock_quantity < 0)
 22    then
 23      raise insufficient_stock;
 24    END IF;
 25  exception
 26    when insufficient_stock then
 27      raise_application_error(-20604,'There is not enoguh stock available');
 28  END;
 29  /

Trigger created.

SQL> show errors
No errors.
SQL> select * from product_stock;

   PROD_ID STOCK_QUANTITY
---------- --------------
         1           1002

SQL> insert into orderline values (1, 1000);

1 row created.

SQL> select * from product_stock;

   PROD_ID STOCK_QUANTITY
---------- --------------
         1              2

SQL> insert into orderline values (1, 3);
insert into orderline values (1, 3)
            *
ERROR at line 1:
ORA-20604: There is not enough stock available
ORA-06512: at "DTD_TRADE.STOCK_CHECK", line 17
ORA-04088: error during execution of trigger 'DTD_TRADE.STOCK_CHECK'


SQL> select * from product_stock;

   PROD_ID STOCK_QUANTITY
---------- --------------
         1              2

update done first to create a lock on purpose (as if two sessions did this call at once, you want the second to block to avoid stock going under 0).

answered Dec 10, 2012 at 15:55

DazzaL

22.1k3 gold badges52 silver badges57 bronze badges

5 Comments

Cathrine Rydning Over a year ago

The trigger is created with no errors. But when I try to test it I get the following errors: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "CRYDNING.STOCK_CHECK", line 13 ORA-04088: error during execution of trigger 'CRYDNING.STOCK_CHECK'

DazzaL Over a year ago

@CathrineRydning make sure that your unique /primary key on product_stock is what i put in my sample where prod_id = :new.prod_id if not, adjust the trigger code to reference 1 row. it must be updating multiple rows (and fails on the returning clause)

Cathrine Rydning Over a year ago

This key is a composite key called prodstock_comp made up by the foreign key product_code and product_size. but if I substitute product_stock for prodstock_comp the trigger has the following compilation error: PLS-00049: bad bind variable 'NEW.PRODSTOCK_COMP'

DazzaL Over a year ago

@CathrineRydning you cant reference a constraint name, so if your orderline table has product_code and product_size that maps to product_stock(product_code, product_size) then use

Update product_stock Set stock_quantity = stock_quantity - v_Newtotal  where product_size = :new.product_size and product_code = :new.product_code returning stock_quantity into v_stock_quantity;

i.e. you want to make sure that given the row in orderline, you're only updating 1 row in product_stock.

Cathrine Rydning Over a year ago

that also menas I need to substitute the "v_stock_quantity product_stock.prodstock_comp%type;" but you cannot use and here, can you?

Collectives™ on Stack Overflow

SQL: Trigger Syntax

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related