Migrating Oracle BEFORE Triggers to Microsoft SQL Server

Handling IBAN and BBAN Logic During INSERT and UPDATE Operations

Overview

When migrating a banking or financial application from Oracle Database to Microsoft SQL Server, one of the most common challenges is trigger compatibility. Oracle and SQL Server implement triggers very differently, especially when it comes to row-level BEFORE triggers.

This article explains:

• Why Oracle BEFORE INSERT OR UPDATE triggers cannot be directly translated to SQL Server
• How to correctly replicate Oracle trigger behavior in SQL Server
• A practical example using IBAN and BBAN banking fields
• Safe and clean migration patterns without altering business logic

The examples are fully anonymized while keeping real-world banking terminology intact.

The Business Rule (IBAN / BBAN)

In many banking systems, the following rules are common:

1. If IBAN is NULL and BBAN is NOT NULL, then:
   • Set iban = bban
2. If BBAN is NULL, then:
   • Set iban = '1234567890' (default or placeholder value)

These rules must apply automatically on INSERT and UPDATE.

Oracle Implementation (Source Behavior)

In Oracle, this logic is typically implemented using a row-level BEFORE trigger, allowing modification of incoming values before they are stored.

Oracle Example

CREATE OR REPLACE TRIGGER trg_UpdateIbanOnInsertOrUpdate
BEFORE INSERT OR UPDATE ON loans_accounts
FOR EACH ROW
BEGIN
    IF :NEW.iban IS NULL AND :NEW.bban IS NOT NULL THEN
        :NEW.iban := :NEW.bban;
    END IF;

    IF :NEW.bban IS NULL THEN
        :NEW.iban := '1234567890';
    END IF;
END;

Why This Works in Oracle

• Oracle allows direct modification of :NEW values
• The trigger fires before data is written
• Logic is applied per row

Why This Does NOT Work in SQL Server

Microsoft SQL Server does not support:

• BEFORE triggers
• FOR EACH ROW
• Direct modification of NEW records

Instead, SQL Server uses:

• Statement-level triggers
• Virtual tables: inserted and deleted

Because of this, Oracle triggers must be redesigned, not copied.

Correct SQL Server Approach

To replicate Oracle BEFORE INSERT OR UPDATE behavior without changing table structure or adding IDs, the best approach is:

✅ Use an INSTEAD OF INSERT, UPDATE Trigger

This allows SQL Server to:

• Intercept incoming data
• Apply IBAN/BBAN logic
• Insert or update rows correctly

SQL Server Trigger (Compatible Equivalent)

For loans_accounts

CREATE TRIGGER trg_UpdateIbanOnInsertOrUpdateLOANS
ON loans_accounts
INSTEAD OF INSERT, UPDATE
AS
BEGIN
    INSERT INTO loans_accounts
    SELECT
        CASE
            WHEN iban IS NULL AND bban IS NOT NULL THEN bban
            WHEN bban IS NULL THEN '1234567890'
            ELSE iban
        END AS iban,
        bban,
        -- all remaining columns in correct order
        col3,
        col4,
        col5
    FROM inserted;
END;

For savings_accounts

CREATE TRIGGER trg_UpdateIbanOnInsertOrUpdateSVGS
ON savings_accounts
INSTEAD OF INSERT, UPDATE
AS
BEGIN
    INSERT INTO savings_accounts
    SELECT
        CASE
            WHEN iban IS NULL AND bban IS NOT NULL THEN bban
            WHEN bban IS NULL THEN '1234567890'
            ELSE iban
        END AS iban,
        bban,
        -- all remaining columns in correct order
        col3,
        col4,
        col5
    FROM inserted;
END;

🔧 Important:
You must list all table columns explicitly in the INSERT INTO and SELECT statements, in the exact same order.

Why This Pattern Is Correct

Best Practices for Banking Systems

• ✅ Keep IBAN/BBAN rules inside the database for data integrity
• ✅ Use INSTEAD OF triggers for Oracle migrations
• ❌ Avoid AFTER triggers when values must be corrected before persistence
• ❌ Do not rely on application-layer fixes for core financial identifiers

Common Migration Pitfalls

• Assuming AFTER INSERT is equivalent to BEFORE INSERT
• Forgetting that SQL Server triggers are statement-based
• Attempting to update the same table inside an AFTER trigger (can cause recursion)
• Not handling multi-row inserts properly

SEO Keywords / Tags

oracle to sql server trigger migration,
oracle before trigger sql server,
iban bban database logic,
sql server instead of trigger,
oracle trigger conversion,
banking database migration,
iban bban sql trigger,
financial system sql server

Final Thoughts

Migrating Oracle triggers to SQL Server is not a copy-paste task—it requires understanding execution models. By using INSTEAD OF INSERT, UPDATE triggers, you can preserve critical banking rules such as IBAN and BBAN normalization without altering schemas or introducing technical debt.

This approach ensures data consistency, regulatory safety, and long-term maintainability in financial systems.