stop trigger

In this PL/SQL trigger I want when stock.quantity=0 to stop inserting into the purchase table in the Oracle database

CREATE OR REPLACE TRIGGER f_trig
AFTER INSERT
ON purchase
FOR EACH ROW
DECLARE
v_quan NUMBER;
BEGIN
SELECT quantity
INTO v_quan
FROM stock
WHERE mal_id = :NEW.mal_id;
IF (v_quan>=:NEW.amount)
THEN
UPDATE stock
SET quantity = (quantity-:NEW.amount)
WHERE mal_id = :NEW.mal_id;
END IF;
END;
/

Before getting into the specifics there are a couple of general points to note about Oracle PL/SQL triggers:
triggers only operate at the transactional level - that is they can't affect what is happening in another transaction
triggers are executed as part of the transaction - that is they are fired automatically - so the logic to dermine what porcessing takes place has to be inside the trigger
Having noted those points, let's look at the specific issue.

The overall logic seems to be that a customer buys something which causes a row to be inserted into the purchase table in the database. Obviously if the customer wants to buy more than is on hand you can't do it so the trigger checks the quantity on hand and only updates the stock table in the Oracle database if the quantity held is greater than or equal to the quantity asked for by the customer.

However if the quantity on hand is less than the quantity ordered by the customer then the stock table is not updated. This seems to be a flaw in the logic - the purchase has been recorded but the stock quantity has not been updated. It is possible there is another part of the application checking for this but if not there should be!

On the specific question of can you stop the insert into the purcahse table if the stock quantity on hand is zero, the answer is no, not with this trigger. By the time the trigger fires the row has already been inserted into the purchase table in the database. This is an after row PL/SQL trigger which means that Oracle fires this trigger after the row has been inserted.

It is possible to abort the whole transaction by raising an exception within the trigger and then performing a rollback at a higher level (Oracle PL/SQL triggers are not allowed to perform either a commit or a rollback). This would be rather a messy solution though and would not prevent another transaction from attempting to do exactly the same thing.

A better, cleaner, more elegant way of doing this would be to have the application handle this. If the quantity on hand is less than the quantity purchased then the purchase should not be allowed. Obviously that would mean an extra query of the database by the application but this would save querying the stock table inside the trigger so the net effect would be no change in terms of load on the database but would make a big difference to the application.

In summary, then, you can't prevent the insert into the purchase table, however by raising an exception in the trigger you can reverse it, but based solely on the limited information provided it would seem as if the application should be re-designed to prevent this from happening.

Posted in: stop trigger