Practical Pl/Sql

Temporal SQL Assertions II: Ignore Legacy Data

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
            || ' and rules are static then '
            || case when static_rules and not dynamic_data then 'unqualified assertions are better' 
                else ' assertions should ignore legacy data' end);
    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 (Temporal SQL Assertions I: Unconditional and Unvalidated) we explored a naive solution that used SQL assertions to validate customer discounts for a customer loyalty program. The basic idea was sound, but did not adapt to the requirements for customer loyalty status to be upgraded over time or loyalty discount minimums to be changed over time.

As a result of the inflexibility of the solution, the only way to upgrade customers or change loyalty discounts was to disable the assertion before performing these updates. After these updates were performed, the assertion could only be enabled in a novalidate state because existing orders violated the assertion.

What if we adapted the assertion to only validate orders placed after the last time the customer loyalty status was updated or the last time the loyalty discount was updated? In this post, I will explore an approach to using an effective date to prevent the assertion from validating ‘legacy‘ orders that were placed when the customer had a different loyalty status.

  1. Getting Started
  2. Use Case Revisited: Customer Loyalty Discounts
  3. Tables
  4. Assertion
  5. View To Review Order Discounts
  6. Sales_API Package
  7. Testing Customer Orders
  8. Testing Upgrading Customer Loyalty Status
  9. Testing Updates To Loyalty Discount Minimums
  10. Reviewing Successful Orders
  11. Summary
  12. TLDR;

Getting Started

The scripts for all examples in this post are available in the practicalplsql repository on github at effective-dated-assertions-2. 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.

Use Case Revisited: Customer Loyalty Discounts

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.

Over time, the minimum required discounts for the various loyalty levels will be changed.

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 data model consists of tables for loyalty, customers and orders. The loyalty table has a minimum discount for each loyalty status. The customers table holds the loyalty status of the customer. The orders table has each customer order with a discount level.

We will add a column to the customers table showing the date of the last update to the customer loyalty status: customers.status_updated. Whenever the customer status is changed, this column will be set to sysdate to show when the change happened.

Similarly, we can add a column to the loyalty table to show when the discount minimum was last changed.

example-2-01-tables.sql

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;
/
-- add status_updated to customers table to support effective dating the assertion
create table if not exists customers(
    customer_name varchar2(10) 
        constraint customers_pk
        primary key,
    status varchar2(10) default 'New' 
        constraint customers_fk_loyalty
        references loyalty(status) not null,
    status_updated date default sysdate not null
)
/
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
)
/

Assertion

We can use the customers.status_updated column to make the assertion only apply the current loyalty discount minimum to orders placed since the last customer status change. The assertion will only consider if orders exist with an insufficient discount where the order.placed date is greater than or equal to the customers.status_updated date.

The assertion should also be adjusted to disregard orders placed before the relevant loyalty discount was updated.

example-2-02-assertion.sql

--assertion only applies to orders created after status was last updated
create assertion if not exists loyalty_discount_applied check (
    not exists (
        select 'current order discount invalid for current customer loyalty status'
        from 
            orders o, 
            customers 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.status_updated
            and o.placed >= s.discount_updated
    )
)
/
Assertion LOYALTY_DISCOUNT_APPLIED created.

This change allows the assertion to ignore older orders without being disabled.

View To Review Order Discounts

As before, we will create a view to review order discounts. Any orders placed prior to the last customer.status_update or loyalty.discount_updated will be marked as “Legacy Order” to show that they will no longer be validated by the assertion.

example-2-03-view.sql

create or replace view review_order_discounts as
select 
    c.customer_name, c.status, (100 * s.discount_min) || '%' as loyalty_discount
    , o.order_id, (100 * o.discount) || '%' as order_discount
    , case 
        when o.placed < greatest(c.status_updated, s.discount_updated) 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
    , c.status_updated
    , s.discount_updated
from 
    customers c 
    join loyalty s on c.status = s.status
    join orders o on c.customer_name = o.customer_name
order by c.customer_name, o.order_id
/

Sales_API Package

To simplify testing, we can create a PL/SQL package to create customers and orders, and update customer loyalty and loyalty discounts. The update_customer_loyalty procedure will ensure that the status_updated column is set to sysdate whenever the customer status is changed. I will just show the package specification and the implementations for update_customer_loyalty and update_loyalty_discount here.

example-2-03-package.sql

--adjust the api package to set customer.status_updated when changing status
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;
/
create or replace package body sales_api
as
...
    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;
    begin
        update customers 
        set status = p_status, status_updated = sysdate
        where customer_name = p_customer_name;
        
        commit;    
        print_tx_state(l_info);
    exception
        when others then
            rollback;
            print_tx_state(l_info, false);
    end update_customer_loyalty;
    procedure update_loyalty_discount(
        p_status in loyalty.status%type,
        p_discount_min in loyalty.discount_min%type)
    is
        l_info t_details := 'update status ' || p_status
            || ' to discount minimum ' || (p_discount_min * 100) || '%';
    begin
        update loyalty
        set discount_min = p_discount_min, discount_updated = sysdate
        where status = p_status;
        
        commit;
        print_tx_state(l_info);
    exception
        when others then
            rollback;
            print_tx_state(l_info, false);
    end update_loyalty_discount;    
end sales_api;
/

Testing Customer Orders

Create customers with the various loyalty status levels and try to create valid or invalid orders for them.

example-2-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.

The assertion only allows orders to be placed if the order discount is at least the same as the customer’s loyalty status minimum discount.

Testing Upgrading Customer Loyalty Status

This is where the previous approach had problems, we were unable to update a customer’s loyalty status without disabling the assertion. Lets try with the new assertion that is aware of the date the customer status changed.

Note: to allow the test script to run in one execution, we will make the session sleep for 5 seconds before making this change so that the new orders all have a greater date than the status update.

exec dbms_session.sleep(5);
prompt upgrading Nina to preferred customer status creates a new effective status period
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;
/
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.

Sucess! We updated Nina’s loyalty status without disabling the assertion. We are also able to place new valid and invalid orders without setting the assertion to a novalidate state! This approach seems like a good solution so far.

Since its always good to test repeatedly, lets upgrade Nina to “Elite” status and see if things are still working.

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 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.

Looking at the view, Nina has been upgraded to Elite and orders placed when she had a lower status now show as ‘Legacy Order’.

Testing Updates To Loyalty Discount Minimums

Our new solution works for placing orders and for upgrading customer loyalty status, will it also work for updating loyalty discount minimums? If the minimum discount changes, orders placed before the loyalty discount changes should not be validated at the new loyalty discount minimum. This should allow the updates to discount minimums to succeed without disabling the assertion.

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

Testing confirms that we can now update the minimum discounts as well without disabling the assertion or setting it to novalidate! This solution meets the requirements better than the first approach.

We can test with some new orders to confirm that the assertion is validating new orders at the new required minimum discounts.

begin
    dbms_output.put_line('#Place valid orders: Prue, Preferred [0.07, 0.065]');
    sales_api.add_order('Prue', 0.07);
    sales_api.add_order('Prue', 0.065);
    dbms_output.put_line('#Place valid order: Nina, Elite, 0.12');
    sales_api.add_order('Nina', 0.12);     
    
    dbms_output.put_line('#Place invalid order: Prue, Preferred, 0.05');
    sales_api.add_order('Prue', 0.05);
    dbms_output.put_line('#Place invalid order: Nina, Elite, 0.10');
    sales_api.add_order('Nina', 0.10); 
      
end;
/
#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.

Reviewing Successful Orders

Looking at the view, any order placed prior to a customer status update or an update for the relevant status discount is now showing as a Legacy Order. These orders were validated when placed, but could no longer be validated if they were not effective dated.

select * from review_order_discounts
/
CUSTOMER_N STATUS     LOYALTY_DISCOUNT     ORDER_ID ORDER_DISCOUNT     DISCOUNT_VALID  PLACED              STATUS_UPDATED      DISCOUNT_UPDATED   
---------- ---------- ------------------ ---------- ------------------ --------------- ------------------- ------------------- -------------------
Liza       Elite      11.25%                      4 10%                Legacy Order    2026-03-16 00:28:05 2026-03-16 00:28:05 2026-03-16 00:28:20
Liza       Elite      11.25%                      5 11%                Legacy Order    2026-03-16 00:28:05 2026-03-16 00:28:05 2026-03-16 00:28:20
Nina       Elite      11.25%                      1 0%                 Legacy Order    2026-03-16 00:28:05 2026-03-16 00:28:15 2026-03-16 00:28:20
Nina       Elite      11.25%                      2 5%                 Legacy Order    2026-03-16 00:28:05 2026-03-16 00:28:15 2026-03-16 00:28:20
Nina       Elite      11.25%                      8 5%                 Legacy Order    2026-03-16 00:28:10 2026-03-16 00:28:15 2026-03-16 00:28:20
Nina       Elite      11.25%                      9 5%                 Legacy Order    2026-03-16 00:28:10 2026-03-16 00:28:15 2026-03-16 00:28:20
Nina       Elite      11.25%                     11 10%                Legacy Order    2026-03-16 00:28:15 2026-03-16 00:28:15 2026-03-16 00:28:20
Nina       Elite      11.25%                     12 11%                Legacy Order    2026-03-16 00:28:15 2026-03-16 00:28:15 2026-03-16 00:28:20
Nina       Elite      11.25%                     16 12%                Exceeds Minimum 2026-03-16 00:28:20 2026-03-16 00:28:15 2026-03-16 00:28:20
Prue       Preferred  6.25%                       3 5%                 Legacy Order    2026-03-16 00:28:05 2026-03-16 00:28:05 2026-03-16 00:28:20
Prue       Preferred  6.25%                      14 7%                 Exceeds Minimum 2026-03-16 00:28:20 2026-03-16 00:28:05 2026-03-16 00:28:20
Prue       Preferred  6.25%                      15 6.5%               Exceeds Minimum 2026-03-16 00:28:20 2026-03-16 00:28:05 2026-03-16 00:28:20

Summary

We have managed to keep the assertion enabled in a validate state by creating conditions that effective date the orders which will be compared to customer loyalty discounts. New orders will always be validated by current conditions, and old orders will be validated because the assertion makes exceptions for them.

This approach works if it is acceptible for the assertion to programatically ignore legacy orders. It would be even better if the assertion always validated each order with the correct discount that is in effect when the order is placed.

The real solution seems to be treating the loyalty discount and the customer status as type 2 slowly changing dimensions. This is the approach we will investigate in the third post of this series.

TLDR;

You can run the full example with the following script:

run-example-2-plsql.sql (or run-example-2-sql.sql for sql based tests)

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

run-example-2-plsql-results.txt (or run-example-2-sql-results.txt for sql tests)

running example one with package api
Table LOYALTY created.
PL/SQL procedure successfully completed.
Table CUSTOMERS 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              STATUS_UPDATED      DISCOUNT_UPDATED   
---------- ---------- ------------------ ---------- ------------------ --------------- ------------------- ------------------- -------------------
Liza       Elite      10%                         4 10%                Meets Minimum   2026-03-16 00:41:57 2026-03-16 00:41:57 2026-03-16 00:41:57
Liza       Elite      10%                         5 11%                Exceeds Minimum 2026-03-16 00:41:57 2026-03-16 00:41:57 2026-03-16 00:41:57
Nina       New        0%                          1 0%                 Meets Minimum   2026-03-16 00:41:57 2026-03-16 00:41:57 2026-03-16 00:41:57
Nina       New        0%                          2 5%                 Exceeds Minimum 2026-03-16 00:41:57 2026-03-16 00:41:57 2026-03-16 00:41:57
Prue       Preferred  5%                          3 5%                 Meets Minimum   2026-03-16 00:41:57 2026-03-16 00:41:57 2026-03-16 00:41:57
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 are not validated by the assertion because their order dates make them legacy orders that arent validated
Nina's new orders after status was upgraded to Preferred satisfy the assertion
CUSTOMER_N STATUS     LOYALTY_DISCOUNT     ORDER_ID ORDER_DISCOUNT     DISCOUNT_VALID  PLACED              STATUS_UPDATED      DISCOUNT_UPDATED   
---------- ---------- ------------------ ---------- ------------------ --------------- ------------------- ------------------- -------------------
Liza       Elite      10%                         4 10%                Meets Minimum   2026-03-16 00:41:57 2026-03-16 00:41:57 2026-03-16 00:41:57
Liza       Elite      10%                         5 11%                Exceeds Minimum 2026-03-16 00:41:57 2026-03-16 00:41:57 2026-03-16 00:41:57
Nina       Preferred  5%                          1 0%                 Legacy Order    2026-03-16 00:41:57 2026-03-16 00:42:02 2026-03-16 00:41:57
Nina       Preferred  5%                          2 5%                 Legacy Order    2026-03-16 00:41:57 2026-03-16 00:42:02 2026-03-16 00:41:57
Nina       Preferred  5%                          8 5%                 Meets Minimum   2026-03-16 00:42:02 2026-03-16 00:42:02 2026-03-16 00:41:57
Nina       Preferred  5%                          9 5%                 Meets Minimum   2026-03-16 00:42:02 2026-03-16 00:42:02 2026-03-16 00:41:57
Prue       Preferred  5%                          3 5%                 Meets Minimum   2026-03-16 00:41:57 2026-03-16 00:41:57 2026-03-16 00:41:57
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, and all earlier orders show as Legacy Orders that are no longer eligible to be checked by the assertion
CUSTOMER_N STATUS     LOYALTY_DISCOUNT     ORDER_ID ORDER_DISCOUNT     DISCOUNT_VALID  PLACED              STATUS_UPDATED      DISCOUNT_UPDATED   
---------- ---------- ------------------ ---------- ------------------ --------------- ------------------- ------------------- -------------------
Liza       Elite      10%                         4 10%                Meets Minimum   2026-03-16 00:41:57 2026-03-16 00:41:57 2026-03-16 00:41:57
Liza       Elite      10%                         5 11%                Exceeds Minimum 2026-03-16 00:41:57 2026-03-16 00:41:57 2026-03-16 00:41:57
Nina       Elite      10%                         1 0%                 Legacy Order    2026-03-16 00:41:57 2026-03-16 00:42:07 2026-03-16 00:41:57
Nina       Elite      10%                         2 5%                 Legacy Order    2026-03-16 00:41:57 2026-03-16 00:42:07 2026-03-16 00:41:57
Nina       Elite      10%                         8 5%                 Legacy Order    2026-03-16 00:42:02 2026-03-16 00:42:07 2026-03-16 00:41:57
Nina       Elite      10%                         9 5%                 Legacy Order    2026-03-16 00:42:02 2026-03-16 00:42:07 2026-03-16 00:41:57
Nina       Elite      10%                        11 10%                Meets Minimum   2026-03-16 00:42:07 2026-03-16 00:42:07 2026-03-16 00:41:57
Nina       Elite      10%                        12 11%                Exceeds Minimum 2026-03-16 00:42:07 2026-03-16 00:42:07 2026-03-16 00:41:57
Prue       Preferred  5%                          3 5%                 Meets Minimum   2026-03-16 00:41:57 2026-03-16 00:41:57 2026-03-16 00:41:57
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              STATUS_UPDATED      DISCOUNT_UPDATED   
---------- ---------- ------------------ ---------- ------------------ --------------- ------------------- ------------------- -------------------
Liza       Elite      11.25%                      4 10%                Legacy Order    2026-03-16 00:41:57 2026-03-16 00:41:57 2026-03-16 00:42:12
Liza       Elite      11.25%                      5 11%                Legacy Order    2026-03-16 00:41:57 2026-03-16 00:41:57 2026-03-16 00:42:12
Nina       Elite      11.25%                      1 0%                 Legacy Order    2026-03-16 00:41:57 2026-03-16 00:42:07 2026-03-16 00:42:12
Nina       Elite      11.25%                      2 5%                 Legacy Order    2026-03-16 00:41:57 2026-03-16 00:42:07 2026-03-16 00:42:12
Nina       Elite      11.25%                      8 5%                 Legacy Order    2026-03-16 00:42:02 2026-03-16 00:42:07 2026-03-16 00:42:12
Nina       Elite      11.25%                      9 5%                 Legacy Order    2026-03-16 00:42:02 2026-03-16 00:42:07 2026-03-16 00:42:12
Nina       Elite      11.25%                     11 10%                Legacy Order    2026-03-16 00:42:07 2026-03-16 00:42:07 2026-03-16 00:42:12
Nina       Elite      11.25%                     12 11%                Legacy Order    2026-03-16 00:42:07 2026-03-16 00:42:07 2026-03-16 00:42:12
Nina       Elite      11.25%                     16 12%                Exceeds Minimum 2026-03-16 00:42:12 2026-03-16 00:42:07 2026-03-16 00:42:12
Prue       Preferred  6.25%                       3 5%                 Legacy Order    2026-03-16 00:41:57 2026-03-16 00:41:57 2026-03-16 00:42:12
Prue       Preferred  6.25%                      14 7%                 Exceeds Minimum 2026-03-16 00:42:12 2026-03-16 00:41:57 2026-03-16 00:42:12
Prue       Preferred  6.25%                      15 6.5%               Exceeds Minimum 2026-03-16 00:42:12 2026-03-16 00:41:57 2026-03-16 00:42:12
12 rows selected. 
drop all objects
View REVIEW_ORDER_DISCOUNTS dropped.
Package SALES_API dropped.
Assertion LOYALTY_DISCOUNT_APPLIED dropped.
Table ORDERS 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