PL/SQL Trigger Lab Exam – 30 Questions with Full Solutions and Explanations

📘 PL/SQL Trigger Lab Exam – 30 Questions

Practice these 30 trigger questions based on the sample database schema for Product, Customer, Orders, Invoice, and InvoiceDetails.

plsql trigger questions, plsql trigger lab exam, before insert trigger, after update trigger, row level trigger plsql, statement level trigger plsql, database triggers examples

🔹 Sample Database Schema

-- Product Table
CREATE TABLE Product (
  ProductID NUMBER PRIMARY KEY,
  ProductName VARCHAR2(50),
  CategoryID NUMBER,
  Price NUMBER
);

-- Customer Table
CREATE TABLE Customer (
  CustomerID NUMBER PRIMARY KEY,
  CustomerName VARCHAR2(50),
  CategoryID NUMBER
);

-- Orders Table
CREATE TABLE Orders (
  OrderID NUMBER PRIMARY KEY,
  CustomerID NUMBER REFERENCES Customer(CustomerID),
  OrderDate DATE,
  OrderType VARCHAR2(20),
  Status VARCHAR2(20)
);

-- Invoice Table
CREATE TABLE Invoice (
  InvoiceID NUMBER PRIMARY KEY,
  OrderID NUMBER REFERENCES Orders(OrderID),
  TotalAmount NUMBER,
  PaymentStatus VARCHAR2(20),
  Discount NUMBER
);

-- InvoiceDetails Table
CREATE TABLE InvoiceDetails (
  InvoiceID NUMBER REFERENCES Invoice(InvoiceID),
  ProductID NUMBER REFERENCES Product(ProductID),
  Quantity NUMBER
);

🔹 PL/SQL Trigger Questions (Q1–Q30)

1. Create a BEFORE INSERT trigger on Product to automatically set ProductID using a sequence.
2. Create an AFTER INSERT trigger on Customer to send a welcome message using DBMS_OUTPUT.
3. Create a BEFORE UPDATE trigger on Product to prevent price from being set below zero.
4. Create an AFTER DELETE trigger on Orders to log deleted order details into a separate table.
5. Create a BEFORE INSERT trigger on Orders to check if CustomerID exists in Customer table.
6. Create a BEFORE UPDATE trigger on Orders to prevent changing status to 'Completed' if TotalAmount = 0.
7. Create a BEFORE DELETE trigger on Product to prevent deletion if the product exists in InvoiceDetails.
8. Create an AFTER INSERT trigger on Invoice to automatically calculate TotalAmount based on InvoiceDetails.
9. Create a BEFORE INSERT trigger on InvoiceDetails to ensure Quantity is greater than zero.
10. Create an AFTER UPDATE trigger on Product to record old and new prices in a PriceLog table.
11. Create a BEFORE INSERT trigger on Customer to prevent duplicate customer names.
12. Create an AFTER DELETE trigger on Customer to delete all associated Orders.
13. Create a BEFORE UPDATE trigger on Invoice to prevent discount from exceeding 50% of TotalAmount.
14. Create an AFTER INSERT trigger on Orders to generate an initial Invoice with zero total.
15. Create a BEFORE INSERT trigger on Orders to restrict OrderType to specific values ('Online', 'Offline').
16. Create an AFTER UPDATE trigger on Invoice to update PaymentStatus to 'Paid' if TotalAmount = 0.
17. Create a BEFORE DELETE trigger on Invoice to prevent deletion if PaymentStatus='Paid'.
18. Create an AFTER INSERT trigger on InvoiceDetails to update TotalAmount in Invoice table.
19. Create a BEFORE UPDATE trigger on Orders to record changes in StatusHistory table.
20. Create a BEFORE INSERT trigger on Product to ensure ProductName is not null or empty.
21. Create an AFTER DELETE trigger on InvoiceDetails to recalculate Invoice TotalAmount.
22. Create a BEFORE UPDATE trigger on Customer to prevent changing CategoryID if TotalOrders > 10.
23. Create an AFTER INSERT trigger on Product to insert initial stock quantity into a Stock table.
24. Create a BEFORE DELETE trigger on Orders to log details into DeletedOrders table for auditing.
25. Create an AFTER UPDATE trigger on Invoice to record old TotalAmount and new TotalAmount into InvoiceHistory.
26. Create a BEFORE INSERT trigger on Invoice to ensure OrderID exists in Orders table.
27. Create a BEFORE UPDATE trigger on Product to prevent changing CategoryID if sales exist for that product.
28. Create an AFTER INSERT trigger on Customer to assign default CategoryID if not provided.
29. Create a BEFORE DELETE trigger on Customer to prevent deletion if pending Orders exist.
30. Create an AFTER UPDATE trigger on Orders to automatically update Invoice TotalAmount if quantities in InvoiceDetails change.

🔹 PL/SQL Trigger Solutions (Q1–Q30)

-- Q1: BEFORE INSERT on Product to set ProductID using sequence
CREATE OR REPLACE TRIGGER trg_product_bi
BEFORE INSERT ON Product
FOR EACH ROW
BEGIN
  :NEW.ProductID := Product_seq.NEXTVAL;
END;
/
-- Explanation: Automatically assigns ProductID from a sequence before inserting.

-- Q2: AFTER INSERT on Customer to send welcome message
CREATE OR REPLACE TRIGGER trg_customer_ai
AFTER INSERT ON Customer
FOR EACH ROW
BEGIN
  DBMS_OUTPUT.PUT_LINE('Welcome, ' || :NEW.CustomerName || '!');
END;
/
-- Explanation: Outputs a welcome message whenever a new customer is inserted.

-- Q3: BEFORE UPDATE on Product to prevent negative price
CREATE OR REPLACE TRIGGER trg_product_bu
BEFORE UPDATE ON Product
FOR EACH ROW
BEGIN
  IF :NEW.Price < 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Price cannot be negative.');
  END IF;
END;
/
-- Explanation: Checks the new price before update and prevents negative values.

-- Q4: AFTER DELETE on Orders to log deleted order
CREATE OR REPLACE TRIGGER trg_orders_ad
AFTER DELETE ON Orders
FOR EACH ROW
BEGIN
  INSERT INTO DeletedOrders(OrderID, CustomerID, OrderDate)
  VALUES(:OLD.OrderID, :OLD.CustomerID, :OLD.OrderDate);
END;
/
-- Explanation: Logs deleted orders into a separate table for audit.

-- Q5: BEFORE INSERT on Orders to check CustomerID exists
CREATE OR REPLACE TRIGGER trg_orders_bi
BEFORE INSERT ON Orders
FOR EACH ROW
DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM Customer WHERE CustomerID = :NEW.CustomerID;
  IF v_count = 0 THEN
    RAISE_APPLICATION_ERROR(-20002, 'CustomerID does not exist.');
  END IF;
END;
/
-- Explanation: Ensures foreign key constraint at trigger level.

-- Q6: BEFORE UPDATE on Orders to prevent completing zero-total orders
CREATE OR REPLACE TRIGGER trg_orders_bu
BEFORE UPDATE ON Orders
FOR EACH ROW
BEGIN
  DECLARE
    v_total NUMBER;
  BEGIN
    SELECT SUM(TotalAmount) INTO v_total FROM Invoice WHERE OrderID = :NEW.OrderID;
    IF v_total = 0 AND :NEW.Status='Completed' THEN
      RAISE_APPLICATION_ERROR(-20003,'Cannot complete order with zero total.');
    END IF;
  END;
END;
/
-- Explanation: Prevents status change to 'Completed' if total invoice is zero.

-- Q7: BEFORE DELETE on Product to prevent deletion if referenced
CREATE OR REPLACE TRIGGER trg_product_bd
BEFORE DELETE ON Product
FOR EACH ROW
DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM InvoiceDetails WHERE ProductID = :OLD.ProductID;
  IF v_count > 0 THEN
    RAISE_APPLICATION_ERROR(-20004,'Cannot delete product with related orders.');
  END IF;
END;
/
-- Explanation: Checks foreign key references before deletion.

-- Q8: AFTER INSERT on Invoice to calculate TotalAmount
CREATE OR REPLACE TRIGGER trg_invoice_ai
AFTER INSERT ON Invoice
FOR EACH ROW
BEGIN
  UPDATE Invoice i
  SET TotalAmount = (SELECT SUM(Quantity * p.Price)
                     FROM InvoiceDetails d
                     JOIN Product p ON d.ProductID = p.ProductID
                     WHERE d.InvoiceID = :NEW.InvoiceID)
  WHERE i.InvoiceID = :NEW.InvoiceID;
END;
/
-- Explanation: Automatically calculates total invoice amount after insertion.

-- Q9: BEFORE INSERT on InvoiceDetails to ensure Quantity > 0
CREATE OR REPLACE TRIGGER trg_invoice_details_bi
BEFORE INSERT ON InvoiceDetails
FOR EACH ROW
BEGIN
  IF :NEW.Quantity <= 0 THEN
    RAISE_APPLICATION_ERROR(-20005,'Quantity must be greater than zero.');
  END IF;
END;
/
-- Explanation: Validates quantity before inserting invoice details.

-- Q10: AFTER UPDATE on Product to log price changes
CREATE OR REPLACE TRIGGER trg_product_au
AFTER UPDATE ON Product
FOR EACH ROW
BEGIN
  IF :OLD.Price <> :NEW.Price THEN
    INSERT INTO PriceLog(ProductID, OldPrice, NewPrice, ChangeDate)
    VALUES(:NEW.ProductID, :OLD.Price, :NEW.Price, SYSDATE);
  END IF;
END;
/
-- Explanation: Logs any price changes for auditing.

-- Q11: BEFORE INSERT on Customer to prevent duplicate names
CREATE OR REPLACE TRIGGER trg_customer_bi
BEFORE INSERT ON Customer
FOR EACH ROW
DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM Customer WHERE CustomerName = :NEW.CustomerName;
  IF v_count > 0 THEN
    RAISE_APPLICATION_ERROR(-20006,'Customer name already exists.');
  END IF;
END;
/
-- Explanation: Prevents inserting duplicate customer names.

-- Q12: AFTER DELETE on Customer to remove associated orders
CREATE OR REPLACE TRIGGER trg_customer_ad
AFTER DELETE ON Customer
FOR EACH ROW
BEGIN
  DELETE FROM Orders WHERE CustomerID = :OLD.CustomerID;
END;
/
-- Explanation: Automatically deletes orders when a customer is deleted.

-- Q13: BEFORE UPDATE on Invoice to limit discount to 50%
CREATE OR REPLACE TRIGGER trg_invoice_bu
BEFORE UPDATE ON Invoice
FOR EACH ROW
BEGIN
  IF :NEW.Discount > (:OLD.TotalAmount * 0.5) THEN
    RAISE_APPLICATION_ERROR(-20007,'Discount cannot exceed 50% of total.');
  END IF;
END;
/
-- Explanation: Validates discount before updating.

-- Q14: AFTER INSERT on Orders to generate initial Invoice
CREATE OR REPLACE TRIGGER trg_orders_ai
AFTER INSERT ON Orders
FOR EACH ROW
BEGIN
  INSERT INTO Invoice(InvoiceID, OrderID, TotalAmount, PaymentStatus, Discount)
  VALUES(Invoice_seq.NEXTVAL, :NEW.OrderID, 0, 'Pending', 0);
END;
/
-- Explanation: Creates a new invoice automatically when a new order is added.

-- Q15: BEFORE INSERT on Orders to restrict OrderType
CREATE OR REPLACE TRIGGER trg_orders_type
BEFORE INSERT ON Orders
FOR EACH ROW
BEGIN
  IF :NEW.OrderType NOT IN ('Online','Offline') THEN
    RAISE_APPLICATION_ERROR(-20008,'Invalid OrderType.');
  END IF;
END;
/
-- Explanation: Ensures only valid order types are inserted.

-- Q16: AFTER UPDATE on Invoice to mark Paid if TotalAmount=0
CREATE OR REPLACE TRIGGER trg_invoice_au
AFTER UPDATE ON Invoice
FOR EACH ROW
BEGIN
  IF :NEW.TotalAmount = 0 THEN
    UPDATE Invoice SET PaymentStatus='Paid' WHERE InvoiceID = :NEW.InvoiceID;
  END IF;
END;
/
-- Explanation: Automatically marks invoices as paid if total is zero.

-- Q17: BEFORE DELETE on Invoice to prevent deletion if Paid
CREATE OR REPLACE TRIGGER trg_invoice_bd
BEFORE DELETE ON Invoice
FOR EACH ROW
BEGIN
  IF :OLD.PaymentStatus='Paid' THEN
    RAISE_APPLICATION_ERROR(-20009,'Cannot delete paid invoice.');
  END IF;
END;
/
-- Explanation: Prevents deletion of paid invoices.

-- Q18: AFTER INSERT on InvoiceDetails to update Invoice TotalAmount
CREATE OR REPLACE TRIGGER trg_invoice_details_ai
AFTER INSERT ON InvoiceDetails
FOR EACH ROW
BEGIN
  UPDATE Invoice i
  SET TotalAmount = TotalAmount + (:NEW.Quantity * (SELECT Price FROM Product WHERE ProductID = :NEW.ProductID))
  WHERE i.InvoiceID = :NEW.InvoiceID;
END;
/
-- Explanation: Updates total invoice after adding invoice details.

-- Q19: BEFORE UPDATE on Orders to record status changes
CREATE OR REPLACE TRIGGER trg_orders_status
BEFORE UPDATE ON Orders
FOR EACH ROW
BEGIN
  IF :OLD.Status <> :NEW.Status THEN
    INSERT INTO StatusHistory(OrderID, OldStatus, NewStatus, ChangeDate)
    VALUES(:NEW.OrderID, :OLD.Status, :NEW.Status, SYSDATE);
  END IF;
END;
/
-- Explanation: Logs order status changes for tracking.

-- Q20: BEFORE INSERT on Product to ensure name is not null
CREATE OR REPLACE TRIGGER trg_product_name
BEFORE INSERT ON Product
FOR EACH ROW
BEGIN
  IF :NEW.ProductName IS NULL THEN
    RAISE_APPLICATION_ERROR(-20010,'ProductName cannot be null.');
  END IF;
END;
/
-- Explanation: Validates product name before insertion.

-- Q21: AFTER DELETE on InvoiceDetails to recalc TotalAmount
CREATE OR REPLACE TRIGGER trg_invoice_details_ad
AFTER DELETE ON InvoiceDetails
FOR EACH ROW
BEGIN
  UPDATE Invoice i
  SET TotalAmount = TotalAmount - (:OLD.Quantity * (SELECT Price FROM Product WHERE ProductID = :OLD.ProductID))
  WHERE i.InvoiceID = :OLD.InvoiceID;
END;
/
-- Explanation: Adjusts invoice total when details are deleted.

-- Q22: BEFORE UPDATE on Customer to prevent changing CategoryID if TotalOrders>10
CREATE OR REPLACE TRIGGER trg_customer_bu
BEFORE UPDATE ON Customer
FOR EACH ROW
DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM Orders WHERE CustomerID = :OLD.CustomerID;
  IF v_count > 10 AND :NEW.CategoryID <> :OLD.CategoryID THEN
    RAISE_APPLICATION_ERROR(-20011,'Cannot change CategoryID for active customer.');
  END IF;
END;
/
-- Explanation: Protects category changes for active customers.

-- Q23: AFTER INSERT on Product to add stock entry
CREATE OR REPLACE TRIGGER trg_product_stock
AFTER INSERT ON Product
FOR EACH ROW
BEGIN
  INSERT INTO Stock(ProductID, Quantity) VALUES(:NEW.ProductID, 0);
END;
/
-- Explanation: Adds initial stock record for new products.

-- Q24: BEFORE DELETE on Orders to log details
CREATE OR REPLACE TRIGGER trg_orders_bd
BEFORE DELETE ON Orders
FOR EACH ROW
BEGIN
  INSERT INTO DeletedOrders(OrderID, CustomerID, OrderDate)
  VALUES(:OLD.OrderID, :OLD.CustomerID, :OLD.OrderDate);
END;
/
-- Explanation: Logs order details before deletion for auditing.

-- Q25: AFTER UPDATE on Invoice to log total changes
CREATE OR REPLACE TRIGGER trg_invoice_au_log
AFTER UPDATE ON Invoice
FOR EACH ROW
BEGIN
  IF :OLD.TotalAmount <> :NEW.TotalAmount THEN
    INSERT INTO InvoiceHistory(InvoiceID, OldTotal, NewTotal, ChangeDate)
    VALUES(:NEW.InvoiceID, :OLD.TotalAmount, :NEW.TotalAmount, SYSDATE);
  END IF;
END;
/
-- Explanation: Tracks changes in invoice total for auditing.

-- Q26: BEFORE INSERT on Invoice to ensure OrderID exists
CREATE OR REPLACE TRIGGER trg_invoice_bi
BEFORE INSERT ON Invoice
FOR EACH ROW
DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM Orders WHERE OrderID = :NEW.OrderID;
  IF v_count = 0 THEN
    RAISE_APPLICATION_ERROR(-20012,'OrderID does not exist.');
  END IF;
END;
/
-- Explanation: Validates foreign key before inserting invoice.

-- Q27: BEFORE UPDATE on Product to prevent changing CategoryID if sales exist
CREATE OR REPLACE TRIGGER trg_product_bu_category
BEFORE UPDATE ON Product
FOR EACH ROW
DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM InvoiceDetails WHERE ProductID = :OLD.ProductID;
  IF v_count > 0 AND :NEW.CategoryID <> :OLD.CategoryID THEN
    RAISE_APPLICATION_ERROR(-20013,'Cannot change category for products with sales.');
  END IF;
END;
/
-- Explanation: Prevents category update for sold products.

-- Q28: AFTER INSERT on Customer to assign default CategoryID
CREATE OR REPLACE TRIGGER trg_customer_ai_default
AFTER INSERT ON Customer
FOR EACH ROW
BEGIN
  IF :NEW.CategoryID IS NULL THEN
    UPDATE Customer SET CategoryID=1 WHERE CustomerID=:NEW.CustomerID;
  END IF;
END;
/
-- Explanation: Assigns a default category for new customers if none is provided.

-- Q29: BEFORE DELETE on Customer to prevent deletion if pending orders exist
CREATE OR REPLACE TRIGGER trg_customer_bd
BEFORE DELETE ON Customer
FOR EACH ROW
DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM Orders WHERE CustomerID = :OLD.CustomerID AND Status='Pending';
  IF v_count > 0 THEN
    RAISE_APPLICATION_ERROR(-20014,'Cannot delete customer with pending orders.');
  END IF;
END;
/
-- Explanation: Protects customers with pending orders from deletion.

-- Q30: AFTER UPDATE on Orders to update Invoice TotalAmount if InvoiceDetails changed
CREATE OR REPLACE TRIGGER trg_orders_au
AFTER UPDATE ON Orders
FOR EACH ROW
BEGIN
  UPDATE Invoice i
  SET TotalAmount = (SELECT SUM(d.Quantity * p.Price)
                     FROM InvoiceDetails d
                     JOIN Product p ON d.ProductID = p.ProductID
                     WHERE d.InvoiceID = i.InvoiceID)
  WHERE i.OrderID = :NEW.OrderID;
END;
/
-- Explanation: Recalculates invoice totals when orders are updated.