Multi-Action SQL Server Transaction Management

Feature requests
1

Hi,

Would it be possible to add transaction boundary controls that can wrap multiple SQL Server actions (DELETE, INSERT, UPDATE) within a single atomic transaction, with configurable error handling for batch processing scenarios.

Currently, each SQL Server action in EasyMorph operates independently without shared transaction context. This creates challenges when performing related operations (e.g., DELETE followed by INSERT) where all actions should succeed or fail together as a single logical unit.

Proposed Solution:
Introduce transaction boundary actions that can encompass multiple SQL Server operations:

Transaction Wrapper Actions:

  • "Begin Transaction" - Start transaction scope (placed before first SQL action)
  • "End Transaction" - Commit/rollback transaction scope (placed after last SQL action)
  • All SQL Server actions between these boundaries execute within the same transaction

Error Handling Options (applies to entire transaction):

  1. "Rollback on Error" - If any batch in any action fails, rollback entire transaction across all actions
  2. "Flag Failed Batches" - Continue processing, add status columns to identify failed batches across all actions
  3. "Halt and Preserve" - Stop at first failure, keep successfully committed batches

Example Workflow:

1. Begin Transaction
2. Delete Action (remove old records)
3. Insert Action (add new records) 
4. Update Action (modify related records)
5. End Transaction

Key Benefits:

  • Logical Consistency: Ensures related operations complete as a unit
  • Cross-Action Rollback: Failed INSERT can trigger rollback of preceding DELETE
  • Simplified Error Handling: Single policy applies to entire operation sequence
  • Data Integrity: Prevents scenarios where DELETE succeeds but INSERT fails

Use Cases:

  • Data replacement workflows (DELETE old + INSERT new)
  • Multi-table synchronization operations
  • Complex ETL processes with dependent steps
  • Any scenario requiring atomic multi-action database operations

This provides true transactional control across multiple related database actions while maintaining EasyMorph's batch processing efficiency.

I would be great if you can look into this.