Changes between Initial Version and Version 1 of Trigger5

Timestamp:

08/21/26 08:00:02 (27 hours ago)

Author:

235018

Comment:

--

Trigger5

@@ +1 @@
+= 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.