⏳ Oracle BEFORE INSERT Trigger: Prevent Out-of-Stock Orders

⏳ Oracle BEFORE INSERT Trigger Tutorial

This tutorial explains Oracle BEFORE INSERT triggers, which automatically execute before a new row is inserted into a table. These triggers are useful for initializing or validating data before insertion. 

It’s often used to validate data, auto-fill values, or prevent invalid transactions.

Before studying and practicing with all types of triggers, you must first create a scenario. Below are example schemas to follow before returning here to continue. Link: Click Here

---

📌 What is a BEFORE INSERT Trigger?

A BEFORE INSERT trigger fires just before an INSERT operation on a table. It allows you to modify or validate the data before it is saved.

📋 Basic Syntax

CREATE OR REPLACE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
  -- Trigger logic here
END;

📋 Example: Automatically Set Creation Date

This trigger sets the created_date column to the current date/time automatically before inserting a new row:

CREATE OR REPLACE TRIGGER trg_before_insert_customer
BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
  :NEW.created_date := SYSDATE;
END;

🔄 Accessing New Row Values

In row-level triggers, you use the :NEW keyword to access or change values in the row being inserted. For example:

• :NEW.column_name — refers to the value for the new row's column.

🔧 Sample Trigger Scenario

We’ll create a before insert trigger that prevents placing an order for a product that has zero quantity in stock.

Trigger Code:

CREATE OR REPLACE TRIGGER trg_check_product_stock
BEFORE INSERT ON orders
FOR EACH ROW
DECLARE
    v_stock NUMBER;
BEGIN
    SELECT quantity_in_stock INTO v_stock
    FROM products
    WHERE product_id = :NEW.product_id;

    IF v_stock = 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Cannot place order. Product is out of stock.');
    END IF;
END;

🔍 How to Test the Trigger

1. Check product stock:

SELECT * FROM products WHERE product_id = 101;
-- Assume quantity_in_stock = 0

2. Try inserting an order:

INSERT INTO orders (order_id, customer_id, product_id, amount, status)
VALUES (1003, 1, 101, 95000, 'PENDING');

Expected Output:

ORA-20001: Cannot place order. Product is out of stock.
ORA-06512: at "TRG_CHECK_PRODUCT_STOCK", line 6

✅ Successful Insert (if stock available)

UPDATE products SET quantity_in_stock = 10 WHERE product_id = 101;

INSERT INTO orders (order_id, customer_id, product_id, amount, status)
VALUES (1004, 1, 101, 95000, 'CONFIRMED');

Now the insert works because the product is in stock.

oracle before insert trigger, oracle trigger testing, oracle trigger validation, plsql before insert example, blogspot oracle trigger, how to prevent invalid insert in oracle, before insert trigger use case, orders products stock trigger example

🔍 How to Test the Trigger

1. Check product stock:

SELECT * FROM products WHERE product_id = 101;
-- Assume quantity_in_stock = 0

2. Try inserting an order:

INSERT INTO orders (order_id, customer_id, product_id, amount, status)
VALUES (1003, 1, 101, 95000, 'PENDING');

Expected Output:

ORA-20001: Cannot place order. Product is out of stock.
ORA-06512: at "TRG_CHECK_PRODUCT_STOCK", line 6

✅ Successful Insert (if stock available)

UPDATE products SET quantity_in_stock = 10 WHERE product_id = 101;

INSERT INTO orders (order_id, customer_id, product_id, amount, status)
VALUES (1004, 1, 101, 95000, 'CONFIRMED');

Now the insert works because the product is in stock.

oracle before insert trigger, oracle trigger testing, oracle trigger validation, plsql before insert example, blogspot oracle trigger, how to prevent invalid insert in oracle, before insert trigger use case, orders products stock trigger example

✅ Summary

• BEFORE INSERT triggers automate tasks before data insertion.
• Use :NEW to access or change new row values.
• Great for setting default values or validating input.

Read the full article on AFTER INSERT, BEFORE UPDATE, AFTER UPDATE, BEFORE DELETE, and AFTER DELETE triggers in Oracle Database.

Oracle BEFORE INSERT triggers help maintain data integrity by running custom logic automatically before inserting data.