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

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.