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

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

{
  "metadata": {
    "UPDATE_CASCADE": {
      "srcKey": "SOURCE_FIELD",
      "applyTo": {
        "TARGET_DIMENSION": {
          "destKey": "TARGET_FIELD",
          "extraCond": {
            "FIELD1": value1,
            "FIELD2": value2
          },
          "body": {
            "FIELD_TO_UPDATE": "new_value"
          }
        }
      }
    }
  }
}

Structure:

• srcKey: Field in the parent record used for matching (e.g., XART03)
• applyTo: Object containing target dimensions to update
  • TARGET_DIMENSION: The dimension where related records will be updated (e.g., ARTCERT)
    • destKey: Field in the target dimension that should match the srcKey value (e.g., XARTCERT04)
    • extraCond: (Optional) Additional conditions to filter which records to update (as object with field-value pairs)
    • body: Object containing fields to update in the matching records

How it works:

1. The value of srcKey from the parent record is used to find matching records in the target dimension
2. Matching is done via: WHERE destKey = srcKey_value
3. Additional filtering applied if extraCond is provided
4. All fields in body are updated in the matching records

Propagates Updates Automatically

Request:

PUT /api/v4/core/ART/45
{
    "source": "artwork-2",
    "debug": "true",
    "XART03": 33,
    "XART04": "test title",
    "metadata": {
        "UPDATE_CASCADE": {
            "srcKey": "XART03",
            "applyTo": {
                "ARTCERT": {
                    "destKey": "XARTCERT04",
                    "extraCond": {
                        "XARTCERT03": 1,
                        "XARTCERT09": 0
                    },
                    "body": {
                        "XARTCERT08": "20251223"
                    }
                }
            }
        }
    }
}

What happens:

1. Parent record (ART) is updated with XART03 = 33 and XART04 = "test title"
2. The metadata.UPDATE_CASCADE configuration triggers:
   • Find records in ARTCERT where XARTCERT04 = 33 (matches XART03 value)
   • AND XARTCERT03 = 1 (from extraCond)
   • AND XARTCERT09 = 0 (from extraCond)
3. Update matching ARTCERT records setting XARTCERT08 = "20251223"

Generated SQL (single transaction):

BEGIN TRANSACTION;

-- 1. Update parent artwork record
UPDATE TB_ANAG_ART00 SET
    LOWNER = '2',
    LDATA = '20251223152423',
    TIMESTAMP = '17664998637936',
    TREC = 'N',
    XART03 = 33,
    XART04 = 'test title'
WHERE ART_ID = 45;

-- 2. Update related certificate records (cascade)
UPDATE TB_ANAG_ARTCERT00 SET
    XARTCERT08 = ?,
    LOWNER = '2',
    LDATA = '20251223152423',
    TREC = 'N'
WHERE XARTCERT04 = ?       -- = 33 (from srcKey XART03)
  AND XARTCERT03 = ?       -- = 1 (from extraCond)
  AND XARTCERT09 = ?;      -- = 0 (from extraCond)
-- affectedRows: 1

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

COMMIT;

Response:

{
    "code": 200,
    "debug": {
        "body": {
            "XART03": 33,
            "XART04": "test title",
            "debug": "true",
            "source": "artwork-2"
        },
        "fields": {
            "ART_ID": 45,
            "XART03": 33,
            "XART04": "test title"
        },
        "metadataDebug": {
            "UPDATE_CASCADE": [
                {
                    "affectedRows": 1,
                    "args": [
                        "20251223",
                        33,
                        1,
                        0
                    ],
                    "sql": "update TB_ANAG_ARTCERT00 set XARTCERT08 = ?,  LOWNER = '2',  LDATA = '20251223152423',  TREC = 'N' where XARTCERT04 = ? and XARTCERT03 = ? and XARTCERT09 = ? "
                }
            ]
        },
        "sql": " update TB_ANAG_ART00 set  LOWNER = '2',  LDATA = '20251223152423',  TIMESTAMP = '17664998637936',  TREC = 'N', XART03 = 33, XART04 = 'test title' where ART_ID = 45"
    },
    "modifiedId": 45,
    "outbox": false
}

Key points in the response:

• modifiedId: 45 - The ID of the updated parent ART record
• metadataDebug.UPDATE_CASCADE - Array containing results for each cascaded update
• affectedRows: 1 - Number of ARTCERT records updated
• args - Actual values used in the SQL query (XARTCERT08 value, srcKey value, extraCond values)
• sql - The actual SQL executed for the cascade update

⚠️ Caution: UPDATE_CASCADE affects ALL related records that match the criteria. Use carefully for fields like price (you may NOT want to update historical orders). Always use extraCond to be selective about which records to update.

Conditional Cascades with extraCond

Using extraCond to selectively update records:

{
  "XART03": 33,
  "metadata": {
    "UPDATE_CASCADE": {
      "srcKey": "XART03",
      "applyTo": {
        "ARTCERT": {
          "destKey": "XARTCERT04",
          "extraCond": {
            "XARTCERT05": "active",
            "XARTCERT09": 0
          },
          "body": {
            "XARTCERT08": "20251223"
          }
        }
      }
    }
  }
}

Result: Only updates ARTCERT records where:

• XARTCERT04 = 33 (matches srcKey)
• AND XARTCERT05 = "active" (from extraCond)
• AND XARTCERT09 = 0 (from extraCond)

DELETE_CASCADE

Metadata Configuration

DELETE_CASCADE is configured via query parameters (not in the request body, since DELETE has no body):

// Metadata configuration as JSON object
{
  "metadata": true,  // REQUIRED - must be true to enable cascade
  "DELETE_CASCADE": {
    "srcKey": "SOURCE_FIELD",
    "applyTo": {
      "TARGET_DIMENSION": "TARGET_FIELD"
    }
  }
}

Structure:

• metadata: (Required) Must be true to enable DELETE_CASCADE
• srcKey: Field in the parent record used for matching (e.g., XART03)
• applyTo: Object containing target dimensions to delete from
  • TARGET_DIMENSION: The dimension where related records will be deleted (e.g., ARTCERT)
  • TARGET_FIELD: Field in the target dimension that should match the srcKey value (e.g., XARTCERT04)

How it works:

1. The value of srcKey from the parent record is used to find matching records in the target dimension
2. Matching is done via: WHERE TARGET_FIELD = srcKey_value
3. All matching records are deleted (soft or force, depending on forceDelete parameter)
4. The JSON must be stringified and URL-encoded when passed as query parameter

Soft Delete Cascade (Default)

Request:

DELETE /api/v4/core/ART/45?metadata={"metadata":true,"DELETE_CASCADE":{"srcKey":"XART03","applyTo":{"ARTCERT":"XARTCERT04"}}}&debug=1

URL-encoded version:

DELETE /api/v4/core/ART/45?metadata=%7B%22metadata%22%3Atrue%2C%22DELETE_CASCADE%22%3A%7B%22srcKey%22%3A%22XART03%22%2C%22applyTo%22%3A%7B%22ARTCERT%22%3A%22XARTCERT04%22%7D%7D%7D&debug=1

What happens:

1. First, get the value of srcKey (XART03) from the parent record with ART_ID = 45
2. Soft delete (TREC='C') all ARTCERT records where XARTCERT04 = value_of_XART03
3. Soft delete parent record (ART with ART_ID = 45)

Generated SQL (soft delete - single transaction):

BEGIN TRANSACTION;

-- 1. Get srcKey value from parent record
SELECT XART03 FROM TB_ANAG_ART00 WHERE ART_ID = 45;
-- Returns: XART03 = 33

-- 2. Soft delete all related certificate records (cascade)
UPDATE TB_ANAG_ARTCERT00
SET TREC = 'C',
    LDATA = '20251223160000',
    LOWNER = '2'
WHERE XARTCERT04 = 33;  -- Value from srcKey XART03

-- 3. Soft delete parent artwork record
UPDATE TB_ANAG_ART00
SET TREC = 'C',
    LDATA = '20251223160000',
    LOWNER = '2'
WHERE ART_ID = 45;

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

COMMIT;

Response:

{
    "ART_ID": 45,
    "code": 200,
    "deletedRows": 1,
    "dim": "ART",
    "outbox": false
}

Key points in the response:

• code: 200 - Success status
• deletedRows: 1 - Number of parent records deleted
• dim: "ART" - The dimension that was deleted
• outbox: false - Whether outbox event was published

Force Delete Cascade

Request (with forceDelete parameter):

DELETE /api/v4/core/ART/45?forceDelete=true&metadata={"metadata":true,"DELETE_CASCADE":{"srcKey":"XART03","applyTo":{"ARTCERT":"XARTCERT04"}}}

What happens:

1. Get the value of srcKey (XART03) from the parent record
2. Physically delete all ARTCERT records where XARTCERT04 = value_of_XART03
3. Physically delete parent record (ART with ART_ID = 45)

Generated SQL (force delete - single transaction):

BEGIN TRANSACTION;

-- 1. Get srcKey value from parent record
SELECT XART03 FROM TB_ANAG_ART00 WHERE ART_ID = 45;
-- Returns: XART03 = 33

-- 2. Force delete all related certificate records (cascade)
DELETE FROM TB_ANAG_ARTCERT00
WHERE XARTCERT04 = 33;  -- Value from srcKey XART03

-- 3. Force delete parent artwork record
DELETE FROM TB_ANAG_ART00
WHERE ART_ID = 45;

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

COMMIT;

⚠️ Warning: Force delete is irreversible. Soft delete (default) is recommended as it preserves data with TREC='C'.

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 updateArtworkWithCascade(artId, artData, cascadeUpdates) {
  const response = await fetch(`${apiBase}/api/v4/core/ART/${artId}`, {
    method: 'PUT',
    headers: {
      'Authorization': `Bearer ${token}`,
      'Content-Type': 'application/json'
    },
    body: JSON.stringify({
      source: 'my-app',
      ...artData,
      metadata: {
        UPDATE_CASCADE: {
          srcKey: 'XART03',
          applyTo: {
            ARTCERT: {
              destKey: 'XARTCERT04',
              extraCond: cascadeUpdates.extraCond,
              body: cascadeUpdates.body
            }
          }
        }
      }
    })
  });

  const result = await response.json();
  console.log('Artwork updated:', result.modifiedId);
  console.log('Cascade affected rows:',
    result.debug.metadataDebug.UPDATE_CASCADE[0].affectedRows);

  return result;
}

// Usage
await updateArtworkWithCascade(
  45,
  {
    XART03: 33,
    XART04: 'test title'
  },
  {
    extraCond: { XARTCERT03: 1, XARTCERT09: 0 },
    body: { XARTCERT08: '20251223' }
  }
);

Delete with Cascades

async function deleteArtworkWithCascade(artId, cascadeConfig, forceDelete = false) {
  // Build metadata query parameter
  const metadata = {
    metadata: true,  // Required
    DELETE_CASCADE: cascadeConfig
  };

  // Stringify and URL-encode the metadata
  const metadataParam = encodeURIComponent(JSON.stringify(metadata));

  // Build URL with query parameters
  const url = `${apiBase}/api/v4/core/ART/${artId}?metadata=${metadataParam}${forceDelete ? '&forceDelete=true' : ''}`;

  const response = await fetch(url, {
    method: 'DELETE',
    headers: { 'Authorization': `Bearer ${token}` }
  });

  const result = await response.json();
  console.log('Artwork deleted:', result.ART_ID);
  console.log('Deleted rows:', result.deletedRows);
  console.log('Delete type:', forceDelete ? 'Physical' : 'Soft (TREC=C)');

  return result;
}

// Usage - Soft delete (default)
await deleteArtworkWithCascade(
  45,
  {
    srcKey: 'XART03',
    applyTo: { ARTCERT: 'XARTCERT04' }
  }
);

// Usage - Force delete
await deleteArtworkWithCascade(
  45,
  {
    srcKey: 'XART03',
    applyTo: { ARTCERT: 'XARTCERT04' }
  },
  true  // forceDelete = true
);

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 soft delete (TREC='C')
const metadata = {
  metadata: true,
  DELETE_CASCADE: {
    srcKey: 'XART03',
    applyTo: { ARTCERT: 'XARTCERT04' }
  }
};
// Don't use forceDelete parameter - soft delete is default

Be selective with UPDATE_CASCADE and use extraCond:

// ✅ Good - only updates active/pending records
{
  "metadata": {
    "UPDATE_CASCADE": {
      "srcKey": "XART03",
      "applyTo": {
        "ARTCERT": {
          "destKey": "XARTCERT04",
          "extraCond": { "XARTCERT05": "active" },  // Only active records
          "body": { "XARTCERT08": "20251223" }
        }
      }
    }
  }
}

❌ DON'T:

Don't cascade updates without extraCond for selective filtering:

// ❌ Bad - updates ALL related records
{
  "metadata": {
    "UPDATE_CASCADE": {
      "srcKey": "XPRD_PRICE",
      "applyTo": {
        "ORDITEM": {
          "destKey": "XORDITEM_PRODUCT_ID",
          // Missing extraCond - affects historical orders!
          "body": { "XORDITEM_PRICE": 99.99 }
        }
      }
    }
  }
}

Don't use forceDelete without good reason:

// ❌ Bad - irreversible physical delete
DELETE /api/v4/core/ART/45?forceDelete=true&metadata=...

// ✅ Good - soft delete first (recoverable)
DELETE /api/v4/core/ART/45?metadata=...

Don't forget the required "metadata": true flag:

// ❌ Bad - DELETE_CASCADE won't work
{
  "DELETE_CASCADE": {
    "srcKey": "XART03",
    "applyTo": { "ARTCERT": "XARTCERT04" }
  }
}

// ✅ Good - includes required metadata flag
{
  "metadata": true,  // Required!
  "DELETE_CASCADE": {
    "srcKey": "XART03",
    "applyTo": { "ARTCERT": "XARTCERT04" }
  }
}

Don't create circular cascades:

// ❌ Bad - infinite loop
// ART cascades to ARTCERT, ARTCERT cascades back to ART
// This will cause cascade conflicts and errors

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