Trigger for Product Management

Consider a database table:

• Products(ProductID, ProductName, Category, Price, StockQuantity)

Tasks:

1. Write a trigger that automatically prevents the Price of any product from being updated to a value less than 50. If such an update is attempted, the trigger should stop the operation and display an appropriate error message.
2. Write another trigger that automatically inserts a record into a separate table Price_Log(ProductID, Old_Price, New_Price, Change_Date) whenever the Price of a product is updated.

Products Table – Price Triggers in PL/SQL

Database Tables

1. Products

ProductID	ProductName	Category	Price	StockQuantity
1	Laptop	Electronics	1000	50
2	Mouse	Electronics	100	200
3	Notebook	Stationery	60	500

2. Price_Log

ProductID	Old_Price	New_Price	Change_Date

---

Trigger 1: Prevent Price Less Than 50

Requirement: Stop updates where Price < 50 and show an error.

CREATE OR REPLACE TRIGGER trg_check_price
BEFORE UPDATE OF Price ON Products
FOR EACH ROW
BEGIN
    IF :NEW.Price < 50 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Price cannot be less than 50!');
    END IF;
END;
/

Example:

UPDATE Products SET Price = 40 WHERE ProductID = 1;
-- Error: Price cannot be less than 50!

---

Trigger 2: Log Price Changes

Requirement: Record every price change into Price_Log with old price, new price, and date.

CREATE OR REPLACE TRIGGER trg_price_log
AFTER UPDATE OF Price ON Products
FOR EACH ROW
BEGIN
    INSERT INTO Price_Log(ProductID, Old_Price, New_Price, Change_Date)
    VALUES(:OLD.ProductID, :OLD.Price, :NEW.Price, SYSDATE);
END;
/

Example:

UPDATE Products SET Price = 120 WHERE ProductID = 2;
-- Products.Price updated
-- Price_Log table now has:
-- | ProductID | Old_Price | New_Price | Change_Date |
-- | 2         | 100       | 120       | 20-FEB-2026 |

---

Notes

• Trigger trg_check_price ensures data integrity by preventing prices below 50.
• Trigger trg_price_log creates an audit trail for price changes.
• Both triggers are row-level (FOR EACH ROW).
• If an update violates trg_check_price, the logging trigger does not fire.