Prevention of deleting stores with existing orders or reports

Description

This trigger prevents a store from being deleted if the store has existing orders or reports associated with it.

A store should not be removed while historical business data still references it, because this could result in the loss of important information about sales, reports, and store activity.

Tables involved

• Store
• Report
• Order
• sells

Type of trigger

• BEFORE DELETE

Activated on the Store table.

SQL Code

CREATE OR REPLACE FUNCTION prevent_store_deletion() 
RETURNS TRIGGER 
LANGUAGE plpgsql 
AS $$ 
BEGIN 
    -- Check whether the store has existing reports 
    IF EXISTS ( SELECT 1 FROM report WHERE store_id = OLD.store_id ) THEN 
        RAISE EXCEPTION 'Store % cannot be deleted because it has existing reports.', OLD.store_id; 
    END IF; 
    -- Check whether the store has products associated with it 
    IF EXISTS ( SELECT 1 FROM sells WHERE store_id = OLD.store_id ) THEN 
        RAISE EXCEPTION 'Store % cannot be deleted because it has existing product records.', OLD.store_id; 
    END IF; 
    RETURN OLD; 
END; 
$$; 

CREATE TRIGGER trg_prevent_store_deletion 
BEFORE DELETE 
ON store 
FOR EACH ROW 
EXECUTE FUNCTION prevent_store_deletion();

Logic explanation

Before a store is deleted, the trigger:

1. Identifies the store using OLD.store_id. 2. Checks whether the store has any reports in the REPORT table. 3. Checks whether the store has any products associated with it through sells. 4. If any historical records exist, the deletion is rejected. 5. If no dependent records exist, the deletion is allowed.

Reason for trigger

This trigger is useful because:

• It protects historical business data.
• It prevents accidental deletion of stores with existing records.
• It maintains referential and business data integrity.
• It ensures that important reports and sales history are not lost.