Cascade Operations

Overview

Cascade operations automatically propagate create, update, and delete operations to related records. This is achieved through explicit configuration in the request body using the metadata key.

Cascade Types:

• INSERT_CASCADE - Create related records on POST
• UPDATE_CASCADE - Update related records on PUT/PATCH
• DELETE_CASCADE - Delete related records on DELETE
• INS_UP_DUPVAL_CASCADE - Insert with ON DUPLICATE KEY UPDATE behavior
• DELETE_BYID_CASCADE - Delete related records by specific ID

Benefits:

• ✅ Single API call creates master + detail records
• ✅ Maintains referential integrity automatically
• ✅ Reduces N+1 query problems
• ✅ Explicitly configured via metadata field in request body
• ✅ Transactional (ACID guarantees)

INSERT_CASCADE

Metadata Configuration

INSERT_CASCADE is configured via the metadata field in the request body:

{
  "metadata": {
    "INSERT_CASCADE": {
      "TARGET_DIMENSION": {
        "replace": {
          "TARGET_FIELD": "SOURCE_FIELD"
        },
        "body": [
          // Array of records to create in target dimension
        ]
      }
    }
  }
}

Structure:

• TARGET_DIMENSION: The dimension where related records will be created (e.g., ARTCERT)
• replace: Field mapping from source to target
  • TARGET_FIELD: Field in the target dimension
  • SOURCE_FIELD: Field from the parent record whose value will be used
• body: Array of record objects to create in the target dimension

How replace works:

• The value from SOURCE_FIELD in the parent record is used to populate TARGET_FIELD in child records
• In the body array, use "TARGET_FIELD": "?" as a placeholder
• The ? will be replaced with the actual value from SOURCE_FIELD after the parent record is created

Single Request Creates Multiple Records

Request:

POST /api/v4/core/ART
[
    {
        "source": "artwork-2",
        "XART01": "",
        "XART02": "",
        "counter": "XART03",
        "XART03": "1",
        "XART19": 1,
        "XART04": "test",
        "XART21": "",
        "XART32": 1,
        "XART22": 1,
        "XART23": "on hold",
        "XART05": 18,
        "XART06": "",
        "XART07": "",
        "XART08": 3,
        "XART36": "",
        "XART09": "0.00",
        "XART10": "0.00",
        "XART11": "0.00",
        "XART12": "cm",
        "XART24": 0,
        "XART25": "0.00",
        "XART26": "0.00",
        "XART27": "0.00",
        "XART28": "0.00",
        "XART29": "cm",
        "XART30": 1,
        "XART13": "0.00",
        "XART14": "EUR",
        "XART16": "",
        "XART17": "no",
        "XART31": "no",
        "XART34": 0,
        "XART37": 1,
        "metadata": {
            "INSERT_CASCADE": {
                "ARTCERT": {
                    "replace": {
                        "XARTCERT04": "XART03"
                    },
                    "body": [
                        {
                            "source": "artwork-2",
                            "XARTCERT03": 0,
                            "counter": "XARTCERT03",
                            "XARTCERT04": "?",
                            "XARTCERT05": "1",
                            "debug": true,
                            "skipCodOnOffCheck": true
                        },
                        {
                            "source": "artwork-2",
                            "XARTCERT03": 0,
                            "counter": "XARTCERT03",
                            "XARTCERT04": "?",
                            "XARTCERT05": "2",
                            "debug": true,
                            "skipCodOnOffCheck": true
                        }
                    ]
                }
            }
        }
    }
]

What happens:

1. Parent record (ART) is created with XART03 counter field auto-generated (e.g., value = 33)
2. The metadata.INSERT_CASCADE.ARTCERT.replace mapping indicates that XARTCERT04 should receive the value of XART03
3. Two child records (ARTCERT) are created:
   • First record: XARTCERT04 = 33 (from parent's XART03), XARTCERT05 = "1", XARTCERT03 counter auto-generated
   • Second record: XARTCERT04 = 33 (from parent's XART03), XARTCERT05 = "2", XARTCERT03 counter auto-generated

Generated SQL (single transaction):

BEGIN TRANSACTION;

-- 1. Insert parent artwork record (with counter generation for XART03)
INSERT INTO TB_ANAG_ART00 (
    XART03, XART04, XART19, XART23, XART05, XART08,
    XART09, XART10, XART11, XART12, XART24, XART25,
    XART26, XART27, XART28, XART29, XART30, XART13,
    XART14, XART17, XART31, XART32, XART22, XART34, XART37,
    OWNER, LOWNER, CDATA, LDATA, TIMESTAMP, TREC
) VALUES (
    33, 'test', 1, 'on hold', 18, 3,
    '0.00', '0.00', '0.00', 'cm', 0, '0.00',
    '0.00', '0.00', '0.00', 'cm', 1, '0.00',
    'EUR', 'no', 'no', 1, 1, 0, 1,
    ?, ?, ?, ?, ?, 'N'
);
-- Returns: insertedId = 43, counter XART03 = 33

-- 2. Insert first certificate record (cascade)
INSERT INTO TB_ANAG_ARTCERT00 (
    XARTCERT03, XARTCERT04, XARTCERT05,
    XARTCERT01, XARTCERT02,
    OWNER, LOWNER, CDATA, LDATA, TIMESTAMP, TREC
) VALUES (
    1, 33, '1',  -- XARTCERT04=33 from parent's XART03, XARTCERT03=1 from counter
    ?, ?,        -- XARTCERT01, XARTCERT02 auto-populated
    ?, ?, ?, ?, ?, 'N'
) ON DUPLICATE KEY UPDATE
    XARTCERT04 = ?, XARTCERT05 = ?,
    XARTCERT01 = ?, XARTCERT02 = ?,
    LOWNER = ?, LDATA = ?, TREC = ?;
-- Returns: insertedId = 1

-- 3. Insert second certificate record (cascade)
INSERT INTO TB_ANAG_ARTCERT00 (
    XARTCERT03, XARTCERT04, XARTCERT05,
    XARTCERT01, XARTCERT02,
    OWNER, LOWNER, CDATA, LDATA, TIMESTAMP, TREC
) VALUES (
    2, 33, '2',  -- XARTCERT04=33 from parent's XART03, XARTCERT03=2 from counter
    ?, ?,        -- XARTCERT01, XARTCERT02 auto-populated
    ?, ?, ?, ?, ?, 'N'
) ON DUPLICATE KEY UPDATE
    XARTCERT04 = ?, XARTCERT05 = ?,
    XARTCERT01 = ?, XARTCERT02 = ?,
    LOWNER = ?, LDATA = ?, TREC = ?;
-- Returns: insertedId = 2

-- 4. Insert outbox event
INSERT INTO OUTBOX (...) VALUES (...);

COMMIT;

Response:

[
    {
        "INSERT_CASCADE": {
            "ARTCERT": [
                {
                    "code": 201,
                    "debug": {
                        "body": {
                            "XARTCERT01": 2,
                            "XARTCERT02": 2,
                            "XARTCERT03": 1,
                            "XARTCERT04": 33,
                            "XARTCERT05": "1",
                            "counter": "XARTCERT03",
                            "debug": true,
                            "skipCodOnOffCheck": true,
                            "source": "artwork-2"
                        },
                        "fields": {
                            "XARTCERT01": 2,
                            "XARTCERT02": 2,
                            "XARTCERT03": 1,
                            "XARTCERT04": 33,
                            "XARTCERT05": "1",
                            "counter": "XARTCERT03",
                            "debug": true,
                            "skipCodOnOffCheck": true,
                            "source": "artwork-2"
                        },
                        "sql": "insert into TB_ANAG_ARTCERT00 (XARTCERT03, XARTCERT04, XARTCERT05, XARTCERT01, XARTCERT02, OWNER, LOWNER, CDATA, LDATA, TIMESTAMP, TREC) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) on duplicate key update XARTCERT04 = ?,XARTCERT05 = ?,XARTCERT01 = ?,XARTCERT02 = ?,LOWNER = ?,LDATA = ?,TREC = ?"
                    },
                    "insertedId": 1
                },
                {
                    "code": 201,
                    "debug": {
                        "body": {
                            "XARTCERT01": 2,
                            "XARTCERT02": 2,
                            "XARTCERT03": 2,
                            "XARTCERT04": 33,
                            "XARTCERT05": "2",
                            "counter": "XARTCERT03",
                            "debug": true,
                            "skipCodOnOffCheck": true,
                            "source": "artwork-2"
                        },
                        "fields": {
                            "XARTCERT01": 2,
                            "XARTCERT02": 2,
                            "XARTCERT03": 2,
                            "XARTCERT04": 33,
                            "XARTCERT05": "2",
                            "counter": "XARTCERT03",
                            "debug": true,
                            "skipCodOnOffCheck": true,
                            "source": "artwork-2"
                        },
                        "sql": "insert into TB_ANAG_ARTCERT00 (XARTCERT03, XARTCERT04, XARTCERT05, XARTCERT01, XARTCERT02, OWNER, LOWNER, CDATA, LDATA, TIMESTAMP, TREC) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) on duplicate key update XARTCERT04 = ?,XARTCERT05 = ?,XARTCERT01 = ?,XARTCERT02 = ?,LOWNER = ?,LDATA = ?,TREC = ?"
                    },
                    "insertedId": 2
                }
            ]
        },
        "code": 201,
        "counter": 33,
        "insertedId": 43,
        "outbox": false,
        "rowCount": 1
    }
]

Key points in the response:

• counter: 33 - The auto-generated value for XART03 in the parent record
• insertedId: 43 - The ID of the parent ART record
• INSERT_CASCADE.ARTCERT - Array containing results for each cascaded ARTCERT record
• Each cascaded record includes:
  • code: 201 - Success status
  • insertedId - The ID of the created certificate record (1, 2)
  • debug object - Contains the actual body sent, final fields, and SQL executed

UPDATE_CASCADE

Metadata Configuration

TB_COST.UPDATE_CASCADE defines automatic propagation:

-- When product name changes, update all order items
INSERT INTO TB_COST (
    COD_DIM,
    NUM_COST,
    COD_VAR,
    UPDATE_CASCADE,
    DESCRIZIONE_COST
) VALUES (
    'PRD',  -- Source dimension
    1,      -- Field position
    'XPRD01',  -- Source field (product name)
    'ORDITEM:XORDITEM_PRODUCT_NAME',  -- Target: Dimension:FieldToUpdate
    'Product name'
);

Format: TargetDimension:FieldToUpdate

• TargetDimension: Dimension to update (ORDITEM)
• FieldToUpdate: Field that should receive new value (XORDITEM_PRODUCT_NAME)

Propagates Updates Automatically

Request:

PATCH /api/v4/core/PRD/123
{
  "data": {
    "XPRD01": "Widget Pro V2"  // Name changed
  }
}

Generated SQL (single transaction):

BEGIN TRANSACTION;

-- 1. Update product
UPDATE TB_ANAG_PRD00
SET XPRD01 = 'Widget Pro V2',
    TREC = 'M',
    LDATA = '20251219160000',
    LOWNER = 'admin@example.com'
WHERE PRD_ID = 123;

-- 2. Update all related order items (cascade)
UPDATE TB_ANAG_ORDITEM00
SET XORDITEM_PRODUCT_NAME = 'Widget Pro V2',
    TREC = 'M',
    LDATA = '20251219160000',
    LOWNER = 'admin@example.com'
WHERE XORDITEM10 = 123;

-- 3. Insert outbox events
INSERT INTO OUTBOX (...) VALUES (...);

COMMIT;

⚠️ Caution: UPDATE_CASCADE affects ALL related records. Use carefully for fields like price (you may NOT want to update historical orders).

Conditional Cascades

Only cascade to active records:

-- Only update active order items (not completed orders)
INSERT INTO TB_COST (COD_DIM, NUM_COST, COD_VAR, UPDATE_CASCADE)
VALUES ('PRD', 1, 'XPRD01', 'ORDITEM:XORDITEM_PRODUCT_NAME:WHERE XORDITEM_STATUS=''pending''');

DELETE_CASCADE

Metadata Configuration

TB_COST.DELETE_CASCADE defines automatic deletion:

-- When order is deleted, also delete order items
INSERT INTO TB_COST (
    COD_DIM,
    NUM_COST,
    COD_VAR,
    DELETE_CASCADE,
    DESCRIZIONE_COST
) VALUES (
    'ORD',  -- Source dimension
    10,     -- Field position
    'items',  -- Relationship name
    'ORDITEM:XORDITEM_ORDER_ID:soft',  -- Target:ForeignKey:Mode
    'Order items'
);

Format: TargetDimension:ForeignKeyField:Mode

• TargetDimension: Dimension to delete from (ORDITEM)
• ForeignKeyField: Field linking to parent (XORDITEM_ORDER_ID)
• Mode: soft (TREC='C') or force (physical delete)

Soft Delete Cascade

Request:

DELETE /api/v4/core/ORD/123

Generated SQL (soft delete):

BEGIN TRANSACTION;

-- 1. Soft delete order
UPDATE TB_ANAG_ORD00
SET TREC = 'C',
    LDATA = '20251219160000',
    LOWNER = 'admin@example.com'
WHERE ORD_ID = 123;

-- 2. Soft delete all order items (cascade)
UPDATE TB_ANAG_ORDITEM00
SET TREC = 'C',
    LDATA = '20251219160000',
    LOWNER = 'admin@example.com'
WHERE XORDITEM_ORDER_ID = 123;

-- 3. Insert outbox events
INSERT INTO OUTBOX (...) VALUES (...);

COMMIT;

Force Delete Cascade

Request:

DELETE /api/v4/core/ORD/123?force=true

Generated SQL (force delete):

BEGIN TRANSACTION;

-- 1. Force delete all order items (cascade)
DELETE FROM TB_ANAG_ORDITEM00
WHERE XORDITEM_ORDER_ID = 123;

-- 2. Force delete order
DELETE FROM TB_ANAG_ORD00
WHERE ORD_ID = 123;

-- 3. Insert outbox events
INSERT INTO OUTBOX (...) VALUES (...);

COMMIT;

Note: Cascade deletes happen BEFORE parent delete to satisfy foreign key constraints.

Common Cascade Patterns

Pattern 1: Master-Detail (1:N)

Order → Order Items using INSERT_CASCADE:

// Request body with metadata configuration
{
  "XORD01": "ORD-2025-001",
  "XORD_CUSTOMER_ID": "cust_123",
  "metadata": {
    "INSERT_CASCADE": {
      "ORDITEM": {
        "replace": {
          "XORDITEM_ORDER_ID": "ORD_ID"
        },
        "body": [
          { "XORDITEM10": "prd_1", "XORDITEM_QTY": 2 },
          { "XORDITEM10": "prd_2", "XORDITEM_QTY": 1 }
        ]
      }
    }
  }
}

Pattern 2: Parent-Child Hierarchy

Category → Subcategories using INSERT_CASCADE:

{
  "XCAT_NAME": "Electronics",
  "metadata": {
    "INSERT_CASCADE": {
      "CAT": {
        "replace": {
          "XCAT_PARENT_ID": "CAT_ID"
        },
        "body": [
          { "XCAT_NAME": "Mobile Phones" },
          { "XCAT_NAME": "Laptops" },
          { "XCAT_NAME": "Accessories" }
        ]
      }
    }
  }
}

JavaScript Examples

Create with Cascades

async function createOrderWithItems(orderData, items) {
  const response = await fetch(`${apiBase}/api/v4/core/ORD`, {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${token}`,
      'Content-Type': 'application/json'
    },
    body: JSON.stringify([{
      ...orderData,
      metadata: {
        INSERT_CASCADE: {
          ORDITEM: {
            replace: {
              XORDITEM_ORDER_ID: 'ORD_ID'
            },
            body: items
          }
        }
      }
    }])
  });

  const result = await response.json();
  console.log('Order created:', result[0].insertedId);
  console.log('Counter:', result[0].counter);
  console.log('Items created:', result[0].INSERT_CASCADE.ORDITEM.length);

  return result;
}

// Usage
await createOrderWithItems(
  {
    source: 'my-app',
    XORD_CUSTOMER_ID: 'cust_123',
    XORD03: 299.97
  },
  [
    { XORDITEM10: 'prd_1', XORDITEM_QTY: 2, XORDITEM09: 99.99 },
    { XORDITEM10: 'prd_2', XORDITEM_QTY: 1, XORDITEM09: 99.99 }
  ]
);

Update with Cascades

async function updateProductName(productId, newName) {
  // Update propagates to all order items automatically
  const response = await fetch(`${apiBase}/api/v4/core/PRD/${productId}`, {
    method: 'PATCH',
    headers: {
      'Authorization': `Bearer ${token}`,
      'Content-Type': 'application/json'
    },
    body: JSON.stringify({
      data: { XPRD01: newName }
    })
  });

  const result = await response.json();
  console.log('Product updated:', result.data.PRD_ID);
  // Cascade updates happen automatically in transaction

  return result;
}

Delete with Cascades

async function deleteOrderWithItems(orderId) {
  // Soft delete cascades to all items automatically
  const response = await fetch(`${apiBase}/api/v4/core/ORD/${orderId}`, {
    method: 'DELETE',
    headers: { 'Authorization': `Bearer ${token}` }
  });

  const result = await response.json();
  console.log('Order soft deleted:', orderId);
  // All items also soft deleted (TREC='C')

  return result;
}

Best Practices

✅ DO:

Use INSERT_CASCADE for master-detail:

// ✅ Good - single request creates order + items
{
  "XORD01": "ORD-2025-001",
  "metadata": {
    "INSERT_CASCADE": {
      "ORDITEM": {
        "replace": { "XORDITEM_ORDER_ID": "ORD_ID" },
        "body": [/* items */]
      }
    }
  }
}

Use soft DELETE_CASCADE by default:

-- ✅ Good - reversible
DELETE_CASCADE = 'ORDITEM:XORDITEM_ORDER_ID:soft'

Be selective with UPDATE_CASCADE:

-- ✅ Good - only reference data
UPDATE_CASCADE = 'ORDITEM:XORDITEM_PRODUCT_NAME'  -- Product name

❌ DON'T:

Don't cascade updates for transactional fields:

-- ❌ Bad - changes historical orders
UPDATE_CASCADE = 'ORDITEM:XORDITEM09'  -- Product price

Don't use force DELETE_CASCADE without reason:

-- ❌ Bad - irreversible
DELETE_CASCADE = 'ORDITEM:XORDITEM_ORDER_ID:force'

-- ✅ Good - soft delete first
DELETE_CASCADE = 'ORDITEM:XORDITEM_ORDER_ID:soft'

Don't create circular cascades:

-- ❌ Bad - infinite loop
-- Order cascades to Items, Items cascade back to Order

Performance Considerations

Cascade Performance

Large cascades impact transaction time:

Order with 1 item:    ~10ms
Order with 10 items:  ~50ms
Order with 100 items: ~500ms
Order with 1000 items: ~5s

Optimization strategies:

1. Batch inserts when possible
2. Use database batch operations
3. Consider async processing for large cascades
4. Monitor transaction duration

Indexing for Cascades

Index foreign key fields:

-- ✅ Good - fast cascade lookups
CREATE INDEX idx_orditem_order_id ON TB_ANAG_ORDITEM00(XORDITEM_ORDER_ID);

Summary

• ✅ INSERT_CASCADE creates related records automatically
• ✅ UPDATE_CASCADE propagates changes to related records
• ✅ DELETE_CASCADE deletes related records (soft or force)
• ✅ INS_UP_DUPVAL_CASCADE provides INSERT with ON DUPLICATE KEY UPDATE behavior
• ✅ DELETE_BYID_CASCADE deletes related records by specific ID
• ✅ Configured via metadata field in request body
• ✅ Transactional (ACID guarantees)
• ✅ Single API call for complex operations
• ✅ Reduces N+1 query problems

Key Takeaways:

1. INSERT_CASCADE configured via metadata field in request body
2. All cascade operations are transactional
3. Use replace mapping to link parent fields to child fields
4. Child records created in same transaction as parent
5. Works with counter fields and pre-insert functions
6. Single-level cascades only (no nested cascades)
7. Soft delete cascade is default (reversible)

Cascade Types:

Cascade Type	Purpose	Configuration
INSERT_CASCADE	Create related records	Via metadata in request body
UPDATE_CASCADE	Update related records	Via metadata in request body
DELETE_CASCADE	Delete related records	Via metadata in request body
INS_UP_DUPVAL_CASCADE	INSERT or UPDATE on duplicate	Via metadata in request body
DELETE_BYID_CASCADE	Delete by specific ID	Via metadata in request body

Next: Outbox Pattern →

Related Concepts

• Create Operations - INSERT_CASCADE
• Update Operations - UPDATE_CASCADE
• Delete Operations - DELETE_CASCADE
• Transactions - ACID guarantees