Create Oracle Triggers for Inventory Billing with Tax & VAT

Auto Generate Billing in Oracle Using Triggers with Tax and VAT

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

This tutorial demonstrates how to automate billing in an Oracle Database system. It includes stock validation, subtotal, tax (5%), VAT (7%), and total calculation using BEFORE INSERT and AFTER INSERT triggers.

Step 1: Create Required Tables

BILLING Table

CREATE TABLE billing (
    billing_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    order_id NUMBER NOT NULL,
    customer_id NUMBER NOT NULL,
    product_id NUMBER NOT NULL,
    unit_price NUMBER(10, 2),
    quantity NUMBER,
    subtotal NUMBER(10, 2),
    tax_amount NUMBER(10, 2),
    vat_amount NUMBER(10, 2),
    total_amount NUMBER(10, 2),
    billing_date DATE DEFAULT SYSDATE,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Step 2: Create AFTER INSERT Trigger

CREATE OR REPLACE TRIGGER trg_generate_billing
AFTER INSERT ON orders
FOR EACH ROW
DECLARE
    v_unit_price   NUMBER(10, 2);
    v_subtotal     NUMBER(10, 2);
    v_tax          NUMBER(10, 2);
    v_vat          NUMBER(10, 2);
    v_total        NUMBER(10, 2);
BEGIN
    SELECT unit_price
    INTO v_unit_price
    FROM products
    WHERE product_id = :NEW.product_id;

    v_subtotal := v_unit_price * :NEW.ordered_quantity;
    v_tax := v_subtotal * 0.05;
    v_vat := v_subtotal * 0.07;
    v_total := v_subtotal + v_tax + v_vat;

    INSERT INTO billing (
        order_id, customer_id, product_id, unit_price, quantity,
        subtotal, tax_amount, vat_amount, total_amount
    ) VALUES (
        :NEW.order_id, :NEW.customer_id, :NEW.product_id, v_unit_price,
        :NEW.ordered_quantity, v_subtotal, v_tax, v_vat, v_total
    );
END;
/

Step 3: Test the Workflow

-- Insert product
INSERT INTO products VALUES (201, 'Printer', 500.00, 10, SYSDATE);

-- Insert customer
INSERT INTO customers VALUES (101, 'Rahim', 'Uddin', 'rahim@example.com', SYSDATE);

-- Place order
INSERT INTO orders (
    order_id, customer_id, product_id, order_date,
    amount, status, ordered_quantity
) VALUES (
    3001, 101, 201, SYSDATE, 0, 'CONFIRMED', 3
);

-- Check billing
SELECT * FROM billing WHERE order_id = 3001;

Result: Subtotal = 1500, Tax = 75, VAT = 105, Total = 1680

Conclusion

This example automates billing with proper validations using Oracle triggers. This structure can be easily extended for discounts, inventory updates, and invoicing systems.

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

oracle billing trigger, oracle vat tax calculation, before insert after insert trigger, auto calculate billing amount, oracle erp trigger example, stock validation trigger, oracle trigger with tax and vat, oracle e-commerce backend schema, database billing trigger example