Changes between Version 7 and Version 8 of AdvancedDatabaseDevelopment


Ignore:
Timestamp:
06/05/26 15:40:19 (6 days ago)
Author:
232012
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • AdvancedDatabaseDevelopment

    v7 v8  
    8282FOR EACH ROW
    8383EXECUTE FUNCTION project.block_double_promo();
    84 }}}
    85 
    86 {{{#!div style="text-align: justify; width: 100%;"
    87   Then we add another trigger function that checks if points are being added to an order that already contains discounted products.
    88 }}}
    89 
    90 {{{
    91 CREATE OR REPLACE FUNCTION project.block_points_on_discounted_order()
    92 RETURNS TRIGGER AS $$
    93 DECLARE
    94     discounted_product_exists BOOLEAN;
    95 BEGIN
    96     SELECT EXISTS (
    97         SELECT 1
    98         FROM project.order_products op
    99         JOIN project.modification_products mp
    100             ON mp.product_id = op.product_id
    101         JOIN project.modifications m
    102             ON m.modification_id = mp.modification_id
    103         WHERE op.order_id = NEW.order_id
    104           AND m.type_of_modification = 'DISCOUNT'
    105           AND m.discount IS NOT NULL
    106     )
    107     INTO discounted_product_exists;
    108 
    109     IF NEW.points_used > 0 AND discounted_product_exists THEN
    110         RAISE EXCEPTION
    111             'Double promotion is not allowed: loyalty points cannot be used on an order containing discounted products.';
    112     END IF;
    113 
    114     RETURN NEW;
    115 END;
    116 $$ LANGUAGE plpgsql;
    117 }}}
    118 
    119 {{{#!div style="text-align: justify; width: 100%;"
    120   Finally, we implement a trigger that runs when loyalty points are added or changed on an order.
    121 }}}
    122 
    123 {{{
    124 CREATE OR REPLACE FUNCTION project.block_points_on_discounted_order()
    125 RETURNS TRIGGER AS $$
    126 DECLARE
    127     discounted_product_exists BOOLEAN;
    128 BEGIN
    129     SELECT EXISTS (
    130         SELECT 1
    131         FROM project.order_products op
    132         JOIN project.modification_products mp
    133             ON mp.product_id = op.product_id
    134         JOIN project.modifications m
    135             ON m.modification_id = mp.modification_id
    136         WHERE op.order_id = NEW.order_id
    137           AND m.type_of_modification = 'DISCOUNT'
    138           AND m.discount IS NOT NULL
    139     )
    140     INTO discounted_product_exists;
    141 
    142     IF NEW.points_used > 0 AND discounted_product_exists THEN
    143         RAISE EXCEPTION
    144             'Double promotion is not allowed: loyalty points cannot be used on an order containing discounted products.';
    145     END IF;
    146 
    147     RETURN NEW;
    148 END;
    149 $$ LANGUAGE plpgsql;
    15084}}}
    15185