CODEWITHSUNDEEP
sqloracle-databasedatabase-trigger
Nikhil Salaria
Nikhil Salaria

Reputation: 3

I want to create trigger but I dont know how to do that. Below is the sample

I want to create trigger but I don't know how to do that. Below is the sample:

CREATE OR REPLACE TRIGGER checkDuration
   after UPDATE on comm_shipping
   FOR EACH ROW
DECLARE
   quantity NUMBER;
BEGIN
   SELECT * FROM comm_orders c
      WHERE C.ORDER_ID = :new.order_id 
      AND c.quantity = 1;
   IF quantity=NULL THEN
      RAISE_APPLICATION_ERROR('no more items');
   END IF;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      NULL;
END;

Upvotes: 0

Views: 34

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65278

Try the following ( I suppose your DBMS is Oracle or DB2 because of RAISE_APPLICATION_ERROR ):

CREATE OR REPLACE TRIGGER checkDuration
AFTER UPDATE ON comm_shipping
FOR EACH ROW
DECLARE
   quantity NUMBER;
BEGIN
   SELECT c.quantity
     INTO quantity   -- this part is missing
     FROM comm_orders c
    WHERE c.order_id = :new.order_id;

    IF quantity is NULL THEN 
       RAISE_APPLICATION_ERROR(-20001,'no more items'); -- you need a code 
                                                        -- between -20000 and -20999
    END IF;
   EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
END;

Upvotes: 1

Related Questions