= 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
{{{#!sql
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.