Changes between Initial Version and Version 1 of Trigger4

Timestamp:

08/21/26 07:48:40 (27 hours ago)

Author:

235018

Comment:

--

Trigger4

@@ +1 @@
+= Automatic deletion of product changes when the product is deleted
+
+=== Description
+This trigger automatically deletes all change-history records associated with a product when that product is deleted.
+
+The CHANGE table stores information about modifications made to products. Since these records are dependent on the product they describe, they should not remain in the database after the corresponding product has been removed.
+
+This maintains referential and logical consistency between PRODUCT and CHANGE.
+
+==== Tables involved
+- Product
+- Change
+- made_on
+
+==== Type of trigger
+- BEFORE DELETE
+Activated on the `Product` table.
+
+==== SQL Code
+{{{#!sql
+CREATE OR REPLACE FUNCTION delete_product_changes()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    -- Delete all changes associated with the product
+    DELETE FROM change
+    WHERE product_code = OLD.code;
+
+    RETURN OLD;
+END;
+$$;
+
+
+CREATE TRIGGER trg_delete_product_changes
+BEFORE DELETE
+ON product
+FOR EACH ROW
+EXECUTE FUNCTION delete_product_changes();
+
+}}}
+
+=== Logic explanation
+
+When a product is deleted, the trigger:
+
+**1.** Retrieves the product's code from OLD.code.
+**2.** Searches the CHANGE table for all records where product_code matches that code.
+**3.** Deletes those change-history records.
+**4.** Allows the original product deletion to continue.
+
+=== Reason for trigger
+
+This trigger is useful because:
+
+- It prevents orphaned change records.
+- It keeps the CHANGE table consistent with PRODUCT.
+- It automatically performs cleanup when a product is deleted.
+- The application does not need to manually delete dependent records.
+- It centralizes deletion logic in the database.