Practical Pl/Sql

Temporal SQL Assertions III: Effective Periods

Anthony Harper

set serveroutput on;
declare
    procedure what_to_choose(true_today in boolean, true_always in boolean)
    is
        dynamic_data boolean;
        static_rules boolean;    
    begin
        dynamic_data := true_today and not true_always;
        static_rules := not dynamic_data;
    
        dbms_output.put_line(
            'When data is ' || case when dynamic_data then 'dynamic' else 'static' end
            || case when static_rules then ' and all rules are static' else ' and some rules are adaptible' end
            || ' then ' || case when static_rules and not dynamic_data then 'unqualified' else 'flexible' end
            || ' assertions are better');
    end what_to_choose;
begin
    what_to_choose(true_today => true, true_always => true);
    what_to_choose(true_today => true, true_always => false);
end;
/

In the previous post of this series (Temporal SQL Assertions II: Ignore Legacy Data) we took a typical enterprise approach to applying business rules only to current data in motion and considering older data to be ‘Legacy’ and not in scope for validation efforts due to changes in circumstances.

The merits of this approach were really that we no longer had to disable an assertion in order to perform some of the updates to the data landscape that provided context for the business rules that the assertion was supposed to validate. This was the shortcoming of the initial naive approach to validating customer loyalty discounts that we took in the first post of the series.

In this post we will begin the process of making the assertion truly effective dated by regarding the current customer loyalty status as a slowly changing dimension with effective periods tracking when each status was valid for a customer.

Without changes to customer loyalty discount minimums, this approach should validate the loyalty status that was actually in effect for the customer at the time the order was placed.

Because of the complexities involved with this approach, I will not yet attempt to make the customer loyalty discount itself effective dated. These will retain the legacy approach to effective dating by using a discount_updated date to make the assertion ignore orders placed prior to the applicable loyalty status being updated.

Getting Started

The scripts for all examples in this post are available in the practicalplsql repository on github at effective-dated-assertions-3. I will add direct links to script files when first referenced.

To work with assertions, you will need to use OCI databases or pull a container to access Oracle AI Database 26 release update 1. See the first article in this series for the compose.yaml file and setup scripts to work with an Oracle Free container.

The Use Case: Customer Loyalty Discounts Based On Effective Status

The company is implementing their new customer loyalty program with guaranteed minimum discounts on customer orders for each loyalty level achieved.

There will be several customer loyalty levels defined, each with a minimum order discount requirement: “New” customers have no minimum discounts, “Preferred” customers receive a 5% discount and “Elite” customers always get a 10% discount.

Customers will start as “New“, and then get upgraded to “Preferred” and “Elite” as they place more orders. When customers are upgraded, their older orders should still be validated at the status grade that the customer had when the order was placed.

Over time, the minimum required discounts for the various loyalty levels will be changed. Orders validated before these changes occur will be considered Legacy Orders and need not be validated again.

Rather than enforcing the loyalty discounts through application code, the company has decided to use the new SQL assertions feature in Oracle 26.1 to ensure that orders meet the minimum loyalty discounts for the customer.

The solution shoud not require disabling the assertion or enabling the assertion in a novalidate state.

Tables

The new data model consists of tables for loyalty, customers, customer_loyalty and orders. The loyalty table has a minimum discount for each loyalty status and a discount_updated date to indicate that the minimums have been changed. The customers table holds only the customer name. The orders table has each customer order with a discount level.

The customer_loyalty table is used to manage the loyalty status that is effective for the customer over time. There is an effective date and and expires date associated with each customer status record. When a customer status is updated, the previous record needs to be expired and the new record effective date is set to sysdate while the expires date remains null.

Each customer should only have one open ended status record that represents the currently effective loyalty status. By adding a virtual column to indicate the current row for each customer with a unique index, we are assured that each customer will only have one row without an expires date value. This could be achieved using an assertion, but the simplicity of a conditional unique constraint with the underlying index for performance in finding the current customer record makes it a better design choice.

The loyalty table will have a discount_updated column to track the last change to a discount minimum for a status. This part of the implementation is still part of the ‘legacy’ workaround from the previous approach and it will result in making the assertion bypass orders when the relevant status discount is updated after the order is placed. If all goes well with basing the customer status on effective date periods in this iteration I will try to eliminate this as well in the next iteration.

example-3-01-tables.sql

--apply legacy style solution to loyalty table
create table if not exists loyalty(
    status varchar2(10) 
        constraint loyalty_pk primary key,
    discount_min number(5,4) default 0 not null,
    discount_updated date default sysdate not null
)
/
--insert the discount minimum for each loyalty status
begin
    insert into loyalty(status, discount_min)
    values
        ('New', 0), ('Preferred', .05), ('Elite', 0.10);
    
    commit;
end;
/
create table if not exists customers(
    customer_name varchar2(10) 
        constraint customers_pk primary key
)
/
--make customer loyalty a slowly changing dimension
--use a unique constraint on a virtual column to enforce a single active row
create table if not exists customer_loyalty(
    customer_name varchar2(10)
        constraint customer_loyalty_fk_customers
        references customers(customer_name),
    status varchar2(10) default 'New' 
        constraint customer_loyalty_fk_loyalty
        references loyalty(status) not null,
    effective date default sysdate not null,
    expires date,
    constraint customer_loyalty_ck_dates 
        check (effective < expires),
    constraint customer_loyalty_pk 
        primary key (customer_name, effective),
    active#row as (nvl2(expires, null, customer_name)) virtual,
    constraint customer_loyalty_u_active#row unique (active#row) deferrable initially deferred
)
/
create table if not exists orders(
    order_id integer generated always as identity 
        constraint orders_pk primary key,
    customer_name varchar2(10)
        constraint orders_fk_customers
        references customers(customer_name) not null,
    discount number(5,4) default 0 not null,
    placed date default sysdate not null
)
/

The Assertion

To update the assertion from the previous approach, we just need to check that the order is placed while the customer loyalty status is effective in addition to checking the last updated date of the discount minimum in the loyalty table. The comparison checks that the order.placed is between customer_loyalty.effective and customer_loyalty.expires, unless the expires date is null. This could be rewritten as o.placed between c.effective and nvl(c.expires, o.placed).

example-3-02-assertion.sql

create assertion if not exists loyalty_discount_applied check (
    not exists (
        select 'order discount invalid for effective customer loyalty status'
        from 
            orders o, 
            customer_loyalty c, 
            loyalty s
        where 
            o.customer_name = c.customer_name and c.status = s.status
            and o.discount < s.discount_min
            and o.placed >= c.effective and (c.expires is null or o.placed < c.expires)
            and o.placed >= s.discount_updated
    )
)
/

View for Reviewing Order Discounts: A Band Join

The view is getting a bit more sophisticated. We now have an indirect band join between the order and the relevant customer loyalty status. I think this is more properly part of the join condition, but for now I will put it into the where clause for clarity. By putting the band join condition in the where clause it is obvious that the query will join multiple customer_loyalty records per order row that need to be filtered based on the order placed date and the effective period of the status.

example-3-03-view.sql

create or replace view review_order_discounts as
select 
    c.customer_name, p.status, (100 * s.discount_min) || '%' as loyalty_discount
    , o.order_id
    , (100 * o.discount) || '%' as order_discount
    , case 
        when s.discount_updated > o.placed then 'Legacy Order'
        when o.discount < s.discount_min then 'Insufficient' 
        when o.discount = s.discount_min then 'Meets Minimum' 
        else 'Exceeds Minimum' 
    end as discount_valid
    , o.placed
    , p.effective
    , p.expires
    , s.discount_updated
from 
    customers c 
    join customer_loyalty p on c.customer_name = p.customer_name
    join loyalty s on p.status = s.status
    join orders o on c.customer_name = o.customer_name 
where
    o.placed >= p.effective and (p.expires is null or o.placed < p.expires)
order by c.customer_name, o.order_id
/

The Sales_API Package

The methods exposed by the api package that I am using to support unit testing remain the same. For the implementations, the update_customer_loyalty and add_customer methods make use the additional table for the effective customer_loyalty status. With these dates set, the assertion will enforce the discount minimum for the status that is in effect for the customer at the time of placing the order.

The update_loyalty_discount implementation sets the discount_updated date to sysdate for the loyalty minimum discount as in the last iteration.

example-3-04-package.sql

create or replace package sales_api
as
    procedure add_customer(
        p_customer_name in customers.customer_name%type, 
        p_status in loyalty.status%type);
        
    procedure add_order(
        p_customer_name in customers.customer_name%type, 
        p_discount in orders.discount%type);
        
    procedure update_customer_loyalty(
        p_customer_name in customers.customer_name%type, 
        p_status in loyalty.status%type);
        
    procedure update_loyalty_discount(
        p_status in loyalty.status%type,
        p_discount_min in loyalty.discount_min%type);           
end sales_api;
/
--modify the package body to handle effective dated customer status
create or replace package body sales_api
as
...
    procedure add_customer(
        p_customer_name in customers.customer_name%type, 
        p_status in loyalty.status%type)
    is
        l_info t_details := 'create customer ' || p_customer_name || ' with status ' || p_status;
    begin
        insert into customers(customer_name)
        values (p_customer_name);
        
        insert into customer_loyalty(customer_name, status)
        values (p_customer_name, p_status);
        commit;        
        print_tx_state(l_info);
    exception
        when others then
            rollback;
            print_tx_state(l_info, false);
    end add_customer;        
...
    procedure update_customer_loyalty(
        p_customer_name in customers.customer_name%type, 
        p_status in loyalty.status%type)
    is
        l_info t_details := 'update customer ' || p_customer_name 
            || ' status to ' || p_status;
        l_date date := sysdate;
    begin
        update customer_loyalty
        set expires = l_date
        where customer_name = p_customer_name and expires is null;
        
        insert into customer_loyalty(customer_name, status, effective)
        values (p_customer_name, p_status, l_date);
        
        commit;    
        print_tx_state(l_info);
    exception
        when others then
            rollback;
            print_tx_state(l_info, false);
    end update_customer_loyalty;
...
end sales_api;
/

Testing Customer Orders

The unit tests for creating customers and placing orders all work correctly. The assertion is still working as intended, accepting valid order discounts and rejecting orders with insufficient discounts for the customer’s status. Using the customer_loyalty table to manage the customer’s status as is an ‘effective’ solution when the customer status is first set.

example-3-05-tests-plsql.sql

begin
    dbms_output.put_line('#Create customer Nina, status New');
    sales_api.add_customer('Nina', 'New');
    dbms_output.put_line('#Place valid orders: Nina, New, [0, 0.05]');    
    sales_api.add_order('Nina', 0);
    sales_api.add_order('Nina', 0.05);
    dbms_output.put_line('#Create customer Prue, status Preferred');
    sales_api.add_customer('Prue', 'Preferred');
    
    dbms_output.put_line('#Place valid order: Prue, Preferred, 0.05');
    sales_api.add_order('Prue', 0.05);
    dbms_output.put_line('#Create customer Liza, Elite');
    sales_api.add_customer('Liza', 'Elite');
    
    dbms_output.put_line('#Place valid orders: Liza, Elite, [0.10,0.11]');
    sales_api.add_order('Liza', 0.10);
    sales_api.add_order('Liza', 0.11);
        
    dbms_output.put_line('#Place invalid order: Prue, Preferred, 0');
    sales_api.add_order('Prue', 0);
    
    dbms_output.put_line('#Place invalid order: Liza, Elite, 0.05');    
    sales_api.add_order('Liza', 0.05);
end;
/
#Create customer Nina, status New
create customer Nina with status New COMMITTED
#Place valid orders: Nina, New, [0, 0.05]
place order for customer Nina with 0% discount COMMITTED
place order for customer Nina with 5% discount COMMITTED
#Create customer Prue, status Preferred
create customer Prue with status Preferred COMMITTED
#Place valid order: Prue, Preferred, 0.05
place order for customer Prue with 5% discount COMMITTED
#Create customer Liza, Elite
create customer Liza with status Elite COMMITTED
#Place valid orders: Liza, Elite, [0.10,0.11]
place order for customer Liza with 10% discount COMMITTED
place order for customer Liza with 11% discount COMMITTED
#Place invalid order: Prue, Preferred, 0
place order for customer Prue with 0% discount
    ROLLED BACK ORA-08601: SQL assertion (PRACTICALPLSQL.LOYALTY_DISCOUNT_APPLIED) violated.
#Place invalid order: Liza, Elite, 0.05
place order for customer Liza with 5% discount
    ROLLED BACK ORA-08601: SQL assertion (PRACTICALPLSQL.LOYALTY_DISCOUNT_APPLIED) violated.

Testing Updates To Customer Status

The next challenge is seeing if the assertion uses the effective status periods to validate each order appropriately and maintain the validation state of orders over time. Updating the customer status will change the conditions to be used for validating future orders, but the conditions that orders were originally validated under will remain constant. Two upgrades to Nina’s status will show us if the assertion is working as designed.

exec dbms_session.sleep(5);
begin
    dbms_output.put_line('#Upgrade Nina to Preferred status');
    sales_api.update_customer_loyalty('Nina', 'Preferred');
    
    dbms_output.put_line('#Place valid orders: Nina, Preferred, [0.05,0.05]');
    sales_api.add_order('Nina', 0.05);
    sales_api.add_order('Nina', 0.05);
    
    dbms_output.put_line('#Place invalid order: Nina, Preferred, 0.01');
    sales_api.add_order('Nina', 0.01);    
end;
/
exec dbms_session.sleep(5);
begin
    dbms_output.put_line('#Upgrade Nina to Elite status');
    sales_api.update_customer_loyalty('Nina', 'Elite');
    
    dbms_output.put_line('#Place valid orders: Nina, Elite, [0.10,0.11]');
    sales_api.add_order('Nina', 0.10);
    sales_api.add_order('Nina', 0.11);
    
    dbms_output.put_line('#Place invalid order: Nina, Elite, 0.05');
    sales_api.add_order('Nina', 0.05);
end;
/
#Upgrade Nina to Preferred status
update customer Nina status to Preferred COMMITTED
#Place valid orders: Nina, Preferred, [0.05,0.05]
place order for customer Nina with 5% discount COMMITTED
place order for customer Nina with 5% discount COMMITTED
#Place invalid order: Nina, Preferred, 0.01
place order for customer Nina with 1% discount
    ROLLED BACK ORA-08601: SQL assertion (PRACTICALPLSQL.LOYALTY_DISCOUNT_APPLIED) violated.
#Upgrade Nina to Elite status
update customer Nina status to Elite COMMITTED
#Place valid orders: Nina, Elite, [0.10,0.11]
place order for customer Nina with 10% discount COMMITTED
place order for customer Nina with 11% discount COMMITTED
#Place invalid order: Nina, Elite, 0.05
place order for customer Nina with 5% discount
    ROLLED BACK ORA-08601: SQL assertion (PRACTICALPLSQL.LOYALTY_DISCOUNT_APPLIED) violated.

Review Orders After Updating Customer Status

Before performing the last test of updating the loyalty discount minimum, lets check the view to see how the valid orders look at this point.

select * from review_order_discounts
/
CUSTOMER_N STATUS     LOYALTY_DISCOUNT     ORDER_ID ORDER_DISCOUNT     DISCOUNT_VALID  PLACED              EFFECTIVE           EXPIRES             DISCOUNT_UPDATED   
---------- ---------- ------------------ ---------- ------------------ --------------- ------------------- ------------------- ------------------- -------------------
Liza       Elite      10%                         4 10%                Meets Minimum   2026-03-16 09:11:08 2026-03-16 09:11:08                     2026-03-16 09:11:07
Liza       Elite      10%                         5 11%                Exceeds Minimum 2026-03-16 09:11:08 2026-03-16 09:11:08                     2026-03-16 09:11:07
Nina       New        0%                          1 0%                 Meets Minimum   2026-03-16 09:11:08 2026-03-16 09:11:08 2026-03-16 09:11:13 2026-03-16 09:11:07
Nina       New        0%                          2 5%                 Exceeds Minimum 2026-03-16 09:11:08 2026-03-16 09:11:08 2026-03-16 09:11:13 2026-03-16 09:11:07
Nina       Preferred  5%                          8 5%                 Meets Minimum   2026-03-16 09:11:13 2026-03-16 09:11:13 2026-03-16 09:11:18 2026-03-16 09:11:07
Nina       Preferred  5%                          9 5%                 Meets Minimum   2026-03-16 09:11:13 2026-03-16 09:11:13 2026-03-16 09:11:18 2026-03-16 09:11:07
Nina       Elite      10%                        11 10%                Meets Minimum   2026-03-16 09:11:18 2026-03-16 09:11:18                     2026-03-16 09:11:07
Nina       Elite      10%                        12 11%                Exceeds Minimum 2026-03-16 09:11:18 2026-03-16 09:11:18                     2026-03-16 09:11:07
Prue       Preferred  5%                          3 5%                 Meets Minimum   2026-03-16 09:11:08 2026-03-16 09:11:08                     2026-03-16 09:11:07

The required loyalty discount at each stage in Nina’s progress from New to Elite status is always less than the order discount for orders placed in that stage. This report shows that the assertion is still able to validate all orders in the system by using effective periods for applying status discount requirements.

Test Updates To Loyalty Discount Minimums

The final set of tests involves changing the discount minimum for Preferred and Elite loyalty status levels. These updates will make previous orders out of scope for the assertion because of the loyalty.discount_updated effective date.

After updating the discount minimums, we can place some orders to confirm that new orders are validated correctly.

begin
    dbms_output.put_line('#Change Preferred to 0.0625 minimum discount');
    sales_api.update_loyalty_discount('Preferred', 0.0625);
    
    dbms_output.put_line('#Change Elite to 0.1125 minimum discount');        
    sales_api.update_loyalty_discount('Elite', 0.1125);
end;
/
#Change Preferred to 0.0625 minimum discount
update status Preferred to discount minimum 6.25% COMMITTED
#Change Elite to 0.1125 minimum discount
update status Elite to discount minimum 11.25% COMMITTED

The new discount requirements were set without errors and new orders are being validated correctly with the new minimums. Everything is working as designed. Looking at the results of the view will show which orders in the system are capable of being revalidated. 

CUSTOMER_N STATUS     LOYALTY_DISCOUNT     ORDER_ID ORDER_DISCOUNT     DISCOUNT_VALID  PLACED              EFFECTIVE           EXPIRES             DISCOUNT_UPDATED   
---------- ---------- ------------------ ---------- ------------------ --------------- ------------------- ------------------- ------------------- -------------------
Liza       Elite      11.25%                      4 10%                Legacy Order    2026-03-16 09:11:08 2026-03-16 09:11:08                     2026-03-16 09:11:23
Liza       Elite      11.25%                      5 11%                Legacy Order    2026-03-16 09:11:08 2026-03-16 09:11:08                     2026-03-16 09:11:23
Nina       New        0%                          1 0%                 Meets Minimum   2026-03-16 09:11:08 2026-03-16 09:11:08 2026-03-16 09:11:13 2026-03-16 09:11:07
Nina       New        0%                          2 5%                 Exceeds Minimum 2026-03-16 09:11:08 2026-03-16 09:11:08 2026-03-16 09:11:13 2026-03-16 09:11:07
Nina       Preferred  6.25%                       8 5%                 Legacy Order    2026-03-16 09:11:13 2026-03-16 09:11:13 2026-03-16 09:11:18 2026-03-16 09:11:23
Nina       Preferred  6.25%                       9 5%                 Legacy Order    2026-03-16 09:11:13 2026-03-16 09:11:13 2026-03-16 09:11:18 2026-03-16 09:11:23
Nina       Elite      11.25%                     11 10%                Legacy Order    2026-03-16 09:11:18 2026-03-16 09:11:18                     2026-03-16 09:11:23
Nina       Elite      11.25%                     12 11%                Legacy Order    2026-03-16 09:11:18 2026-03-16 09:11:18                     2026-03-16 09:11:23
Nina       Elite      11.25%                     16 12%                Exceeds Minimum 2026-03-16 09:11:23 2026-03-16 09:11:18                     2026-03-16 09:11:23
Prue       Preferred  6.25%                       3 5%                 Legacy Order    2026-03-16 09:11:08 2026-03-16 09:11:08                     2026-03-16 09:11:23
Prue       Preferred  6.25%                      14 7%                 Exceeds Minimum 2026-03-16 09:11:23 2026-03-16 09:11:08                     2026-03-16 09:11:23
Prue       Preferred  6.25%                      15 6.5%               Exceeds Minimum 2026-03-16 09:11:23 2026-03-16 09:11:08                     2026-03-16 09:11:23

Just like the previous Ignore Legacy approach, orders which were validated on placement but no longer fall into scope of the assertion are marked as Legacy Orders. The discount_updated effective date makes these orders irrelevant to the current state of the status discounts for Preferred and Elite. The report is still not exactly showing a glowing success of the Customer Loyalty Discounts program.

Summary

In the summary of the previous post I said that the goal would be to make Customer Status and Loyalty Discount Minimum into Type 2 Slowly Changing Dimensions interacting appropriately with the business rules using assertions. We have accomplished that with the Customer Status in this iteration. Now the proof of concept is confirmed for using assertions with effectivity periods.

Leaving the Loyalty Discount Minimum using the discount_updated technique left the full solution only half implemented. This allowed us to see both approaches (Legacy Effective Dating and Effectivity Periods) in a single iteration for easy comparison. The Legacy Effective Dating technique is starting to look like a convenience used to keep the appearance of simplicity.

Based on what we have learned here, in the next post we can finalize the approach of using assertions to validate multiple convergent slowly changing dimensions!

TLDR;

run-example-3-plsql.sql (or run-example-3.sql.sql)

spool run-example-3-plsql-results.txt
prompt running example with plsql api
@example-3-01-tables.sql
@example-3-02-assertion.sql
@example-3-03-view.sql
@example-3-04-package.sql
@example-3-05-tests-plsql.sql
@example-3-06-drop-all.sql
spool off

run-example-3-plsql-results.txt (or run-example-3-sql-results.txt)

running example with plsql api
Table LOYALTY created.
PL/SQL procedure successfully completed.
Table CUSTOMERS created.
Table CUSTOMER_LOYALTY created.
Table ORDERS created.
Assertion LOYALTY_DISCOUNT_APPLIED created.
View REVIEW_ORDER_DISCOUNTS created.
Package SALES_API compiled
Package Body SALES_API compiled
#Create customer Nina, status New
create customer Nina with status New COMMITTED
#Place valid orders: Nina, New, [0, 0.05]
place order for customer Nina with 0% discount COMMITTED
place order for customer Nina with 5% discount COMMITTED
#Create customer Prue, status Preferred
create customer Prue with status Preferred COMMITTED
#Place valid order: Prue, Preferred, 0.05
place order for customer Prue with 5% discount COMMITTED
#Create customer Liza, Elite
create customer Liza with status Elite COMMITTED
#Place valid orders: Liza, Elite, [0.10,0.11]
place order for customer Liza with 10% discount COMMITTED
place order for customer Liza with 11% discount COMMITTED
#Place invalid order: Prue, Preferred, 0
place order for customer Prue with 0% discount
    ROLLED BACK ORA-08601: SQL assertion (PRACTICALPLSQL.LOYALTY_DISCOUNT_APPLIED) violated.
#Place invalid order: Liza, Elite, 0.05
place order for customer Liza with 5% discount
    ROLLED BACK ORA-08601: SQL assertion (PRACTICALPLSQL.LOYALTY_DISCOUNT_APPLIED) violated.
PL/SQL procedure successfully completed.
all valid orders have discount minimum that matches customer status
CUSTOMER_N STATUS     LOYALTY_DISCOUNT     ORDER_ID ORDER_DISCOUNT     DISCOUNT_VALID  PLACED              EFFECTIVE           EXPIRES             DISCOUNT_UPDATED   
---------- ---------- ------------------ ---------- ------------------ --------------- ------------------- ------------------- ------------------- -------------------
Liza       Elite      10%                         4 10%                Meets Minimum   2026-03-16 10:26:28 2026-03-16 10:26:28                     2026-03-16 10:26:28
Liza       Elite      10%                         5 11%                Exceeds Minimum 2026-03-16 10:26:28 2026-03-16 10:26:28                     2026-03-16 10:26:28
Nina       New        0%                          1 0%                 Meets Minimum   2026-03-16 10:26:28 2026-03-16 10:26:28                     2026-03-16 10:26:28
Nina       New        0%                          2 5%                 Exceeds Minimum 2026-03-16 10:26:28 2026-03-16 10:26:28                     2026-03-16 10:26:28
Prue       Preferred  5%                          3 5%                 Meets Minimum   2026-03-16 10:26:28 2026-03-16 10:26:28                     2026-03-16 10:26:28
PL/SQL procedure successfully completed.
upgrading Nina to preferred customer status creates a new effective status period
#Upgrade Nina to Preferred status
update customer Nina status to Preferred COMMITTED
#Place valid orders: Nina, Preferred, [0.05,0.05]
place order for customer Nina with 5% discount COMMITTED
place order for customer Nina with 5% discount COMMITTED
#Place invalid order: Nina, Preferred, 0.01
place order for customer Nina with 1% discount
    ROLLED BACK ORA-08601: SQL assertion (PRACTICALPLSQL.LOYALTY_DISCOUNT_APPLIED) violated.
PL/SQL procedure successfully completed.
Nina's original orders still satisfy the assertion based on the status that was effective (New)
Nina's new orders after status was upgraded to Preferred also satisfy the assertion
CUSTOMER_N STATUS     LOYALTY_DISCOUNT     ORDER_ID ORDER_DISCOUNT     DISCOUNT_VALID  PLACED              EFFECTIVE           EXPIRES             DISCOUNT_UPDATED   
---------- ---------- ------------------ ---------- ------------------ --------------- ------------------- ------------------- ------------------- -------------------
Liza       Elite      10%                         4 10%                Meets Minimum   2026-03-16 10:26:28 2026-03-16 10:26:28                     2026-03-16 10:26:28
Liza       Elite      10%                         5 11%                Exceeds Minimum 2026-03-16 10:26:28 2026-03-16 10:26:28                     2026-03-16 10:26:28
Nina       New        0%                          1 0%                 Meets Minimum   2026-03-16 10:26:28 2026-03-16 10:26:28 2026-03-16 10:26:33 2026-03-16 10:26:28
Nina       New        0%                          2 5%                 Exceeds Minimum 2026-03-16 10:26:28 2026-03-16 10:26:28 2026-03-16 10:26:33 2026-03-16 10:26:28
Nina       Preferred  5%                          8 5%                 Meets Minimum   2026-03-16 10:26:33 2026-03-16 10:26:33                     2026-03-16 10:26:28
Nina       Preferred  5%                          9 5%                 Meets Minimum   2026-03-16 10:26:33 2026-03-16 10:26:33                     2026-03-16 10:26:28
Prue       Preferred  5%                          3 5%                 Meets Minimum   2026-03-16 10:26:28 2026-03-16 10:26:28                     2026-03-16 10:26:28
7 rows selected. 
PL/SQL procedure successfully completed.
#Upgrade Nina to Elite status
update customer Nina status to Elite COMMITTED
#Place valid orders: Nina, Elite, [0.10,0.11]
place order for customer Nina with 10% discount COMMITTED
place order for customer Nina with 11% discount COMMITTED
#Place invalid order: Nina, Elite, 0.05
place order for customer Nina with 5% discount
    ROLLED BACK ORA-08601: SQL assertion (PRACTICALPLSQL.LOYALTY_DISCOUNT_APPLIED) violated.
PL/SQL procedure successfully completed.
Nina's status is updated to Elite, all earlier orders show valid discounts for the status that was effective when the orders were placed
CUSTOMER_N STATUS     LOYALTY_DISCOUNT     ORDER_ID ORDER_DISCOUNT     DISCOUNT_VALID  PLACED              EFFECTIVE           EXPIRES             DISCOUNT_UPDATED   
---------- ---------- ------------------ ---------- ------------------ --------------- ------------------- ------------------- ------------------- -------------------
Liza       Elite      10%                         4 10%                Meets Minimum   2026-03-16 10:26:28 2026-03-16 10:26:28                     2026-03-16 10:26:28
Liza       Elite      10%                         5 11%                Exceeds Minimum 2026-03-16 10:26:28 2026-03-16 10:26:28                     2026-03-16 10:26:28
Nina       New        0%                          1 0%                 Meets Minimum   2026-03-16 10:26:28 2026-03-16 10:26:28 2026-03-16 10:26:33 2026-03-16 10:26:28
Nina       New        0%                          2 5%                 Exceeds Minimum 2026-03-16 10:26:28 2026-03-16 10:26:28 2026-03-16 10:26:33 2026-03-16 10:26:28
Nina       Preferred  5%                          8 5%                 Meets Minimum   2026-03-16 10:26:33 2026-03-16 10:26:33 2026-03-16 10:26:38 2026-03-16 10:26:28
Nina       Preferred  5%                          9 5%                 Meets Minimum   2026-03-16 10:26:33 2026-03-16 10:26:33 2026-03-16 10:26:38 2026-03-16 10:26:28
Nina       Elite      10%                        11 10%                Meets Minimum   2026-03-16 10:26:38 2026-03-16 10:26:38                     2026-03-16 10:26:28
Nina       Elite      10%                        12 11%                Exceeds Minimum 2026-03-16 10:26:38 2026-03-16 10:26:38                     2026-03-16 10:26:28
Prue       Preferred  5%                          3 5%                 Meets Minimum   2026-03-16 10:26:28 2026-03-16 10:26:28                     2026-03-16 10:26:28
9 rows selected. 
changing loyalty discounts can be done with assertion enabled, existing data that violates the assertion is ignored
PL/SQL procedure successfully completed.
#Change Preferred to 0.0625 minimum discount
update status Preferred to discount minimum 6.25% COMMITTED
#Change Elite to 0.1125 minimum discount
update status Elite to discount minimum 11.25% COMMITTED
PL/SQL procedure successfully completed.
#Place valid orders: Prue, Preferred [0.07, 0.065]
place order for customer Prue with 7% discount COMMITTED
place order for customer Prue with 6.5% discount COMMITTED
#Place valid order: Nina, Elite, 0.12
place order for customer Nina with 12% discount COMMITTED
#Place invalid order: Prue, Preferred, 0.05
place order for customer Prue with 5% discount
    ROLLED BACK ORA-08601: SQL assertion (PRACTICALPLSQL.LOYALTY_DISCOUNT_APPLIED) violated.
#Place invalid order: Nina, Elite, 0.10
place order for customer Nina with 10% discount
    ROLLED BACK ORA-08601: SQL assertion (PRACTICALPLSQL.LOYALTY_DISCOUNT_APPLIED) violated.
PL/SQL procedure successfully completed.
while new orders will be validated by the assertion, existing orders that would not meet the assertion are ignored (shown as Legacy Order)
CUSTOMER_N STATUS     LOYALTY_DISCOUNT     ORDER_ID ORDER_DISCOUNT     DISCOUNT_VALID  PLACED              EFFECTIVE           EXPIRES             DISCOUNT_UPDATED   
---------- ---------- ------------------ ---------- ------------------ --------------- ------------------- ------------------- ------------------- -------------------
Liza       Elite      11.25%                      4 10%                Legacy Order    2026-03-16 10:26:28 2026-03-16 10:26:28                     2026-03-16 10:26:43
Liza       Elite      11.25%                      5 11%                Legacy Order    2026-03-16 10:26:28 2026-03-16 10:26:28                     2026-03-16 10:26:43
Nina       New        0%                          1 0%                 Meets Minimum   2026-03-16 10:26:28 2026-03-16 10:26:28 2026-03-16 10:26:33 2026-03-16 10:26:28
Nina       New        0%                          2 5%                 Exceeds Minimum 2026-03-16 10:26:28 2026-03-16 10:26:28 2026-03-16 10:26:33 2026-03-16 10:26:28
Nina       Preferred  6.25%                       8 5%                 Legacy Order    2026-03-16 10:26:33 2026-03-16 10:26:33 2026-03-16 10:26:38 2026-03-16 10:26:43
Nina       Preferred  6.25%                       9 5%                 Legacy Order    2026-03-16 10:26:33 2026-03-16 10:26:33 2026-03-16 10:26:38 2026-03-16 10:26:43
Nina       Elite      11.25%                     11 10%                Legacy Order    2026-03-16 10:26:38 2026-03-16 10:26:38                     2026-03-16 10:26:43
Nina       Elite      11.25%                     12 11%                Legacy Order    2026-03-16 10:26:38 2026-03-16 10:26:38                     2026-03-16 10:26:43
Nina       Elite      11.25%                     16 12%                Exceeds Minimum 2026-03-16 10:26:44 2026-03-16 10:26:38                     2026-03-16 10:26:43
Prue       Preferred  6.25%                       3 5%                 Legacy Order    2026-03-16 10:26:28 2026-03-16 10:26:28                     2026-03-16 10:26:43
Prue       Preferred  6.25%                      14 7%                 Exceeds Minimum 2026-03-16 10:26:44 2026-03-16 10:26:28                     2026-03-16 10:26:43
Prue       Preferred  6.25%                      15 6.5%               Exceeds Minimum 2026-03-16 10:26:44 2026-03-16 10:26:28                     2026-03-16 10:26:43
12 rows selected. 
drop all objects
View REVIEW_ORDER_DISCOUNTS dropped.
Package SALES_API dropped.
Assertion LOYALTY_DISCOUNT_APPLIED dropped.
Table ORDERS dropped.
Table CUSTOMER_LOYALTY dropped.
Table CUSTOMERS dropped.
Table LOYALTY dropped.

–Anthony Harper

Recent

Categories

Archives

Source code for this article can be found at https://github.com/gaiansentience/practicalplsql

Posted

in

by

Anthony Harper

, ,
Discussion and Comments

Leave a comment