แก้ไข

O2SS0028: ROWID column generated (Info)

This article describes the reason why SQL Server Migration Assistant (SSMA) for Oracle adds ROWID column to the table, if there are triggers.

Background

In Oracle, you can create a trigger that runs FOR EACH ROW, rather than for the entire set of rows that is changing. In SQL Server, triggers always execute for the entire set of modified rows. If row-level Oracle trigger accesses both - :old and :new special variables, then SSMA needs a way to match rows from both row sets in order to identify what the value was for a given row before and after the update. To emulate such for each row functionality, SSMA adds special ROWID column to uniquely identify each modified row and uses it to establish a relationship between inserted and deleted row sets.

Example

In the following example, an Oracle trigger executes for each row updated in TRIG_TEST table:

CREATE OR REPLACE TRIGGER TSCHM.TRIG_TEST_AU
AFTER UPDATE OF DATA ON TSCHM.TRIG_TEST
FOR EACH ROW
BEGIN
    IF (:new.DATA = 'ABC') THEN
        INSERT INTO TSCHM.TRIG_TEST(DATA) VALUES ('-' || :old.DATA);
    END IF;
END;

When you try to convert this trigger in SSMA, the following T-SQL is produced within the SQL Server trigger:

  1. Run a cursor over inserted row set, selecting ROWID and DATA columns into @new$0 and @new$DATA variables:

    DECLARE
    ForEachInsertedRowTriggerCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
    SELECT ROWID, DATA
    FROM inserted

OPEN ForEachInsertedRowTriggerCursor

FETCH ForEachInsertedRowTriggerCursor
    INTO @new$0, @new$DATA

  2. In a loop, select matching row from deleted row set into @old$0 and @old$DATA variables, based on the inserted ROWID (stored in @new$0 variable):

    SELECT @old$0 = ROWID, @old$DATA = DATA
FROM deleted
WHERE ROWID = @new$0

  3. Perform trigger actions using @old$DATA/@new$DATA variables:

    IF (@new$DATA = 'ABC')
    INSERT SSMAADMIN.TRIG_TEST(DATA)
    VALUES (('-' + ISNULL(@old$DATA, '')))

Additional information

This behavior is controlled by the Generate ROWID column project setting, which can be found under Tools > Project Settings > General > Conversion > ROWID generation. If the setting is set to No, but during trigger conversion SSMA identifies that it requires a ROWID column, then O2SS0239 conversion error is generated.

  • Last updated on