Generation of ROWID column in triggers even when i

问题:

I used SSMA v6.0 for migrating my Oracle database to SQL Server 2014.

I turned off the generation of ROWID column and as expected it did not generate any additional ROWID column in any of my tables after conversion, but surprisingly it DID generate all the triggers associated with their respective tables with ROWID column infused in triggers like following:

USE [DBName]
GO
/****** Object:  Trigger [dbo].[InsteadOfInsertOn$ROLEPERMISSIONS]    Script Date: 3/11/2015 10:58:46 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[InsteadOfInsertOn$ROLEPERMISSIONS]
   ON [dbo].[ROLEPERMISSIONS]
    INSTEAD OF INSERT
      AS 
         /*Generated by SQL Server Migration Assistant for Oracle version 6.0.0.*/
         BEGIN

        SET  NOCOUNT  ON

        DECLARE
           @triggerType char(1)

        SELECT @triggerType = 'I'

        /* column variables declaration*/
        DECLARE
           @new$0 uniqueidentifier, 
           @new$ROLEID decimal, 
           @new$MODULES_ACTIONSID decimal, 
           @new$ROLEPERMISSIONID decimal

        /* 
        *   SSMA error messages:
        *   O2SS0239: ROWID column is not accessible because the 'Generate ROWID' project setting is disabled.

        DECLARE
            ForEachInsertedRowTriggerCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR 
              SELECT ROWID, ROLEID, MODULES_ACTIONSID, ROLEPERMISSIONID
              FROM inserted            */


        /* 
        *   SSMA error messages:
        *   O2SS0174: The declaration of the identifier 'ForEachInsertedRowTriggerCursor' was converted with error(s).

        OPEN ForEachInsertedRowTriggerCursor            */

        /* 
        *   SSMA error messages:
        *   O2SS0174: The declaration of the identifier 'ForEachInsertedRowTriggerCursor' was converted with error(s).

        FETCH ForEachInsertedRowTriggerCursor
            INTO @new$0, @new$ROLEID, @new$MODULES_ACTIONSID, @new$ROLEPERMISSIONID            */

        WHILE @@fetch_status = 0
        BEGIN
              /* row-level triggers implementation: begin*/
              BEGIN
                 BEGIN
                    IF @triggerType = 'I'
                       SELECT @new$ROLEPERMISSIONID = NEXT VALUE FOR dbo.SEQ_ROLEPERMISSIONS
                 END
              END
              /* row-level triggers implementation: end*/

              /* 
              *   SSMA error messages:
              *   O2SS0239: ROWID column is not accessible because the 'Generate ROWID' project setting is disabled.

              /-* DML-operation emulation*-/
              INSERT dbo.ROLEPERMISSIONS(ROWID, ROLEID, MODULES_ACTIONSID, ROLEPERMISSIONID)
                 VALUES (@new$0, @new$ROLEID, @new$MODULES_ACTIONSID, @new$ROLEPERMISSIONID)                  */



              /* 
              *   SSMA error messages:
              *   O2SS0174: The declaration of the identifier 'ForEachInsertedRowTriggerCursor' was converted with error(s).

              FETCH ForEachInsertedRowTriggerCursor
                  INTO @new$0, @new$ROLEID, @new$MODULES_ACTIONSID, @new$ROLEPERMISSIONID                  */

           END

        /* 
        *   SSMA error messages:
        *   O2SS0174: The declaration of the identifier 'ForEachInsertedRowTriggerCursor' was converted with error(s).

        CLOSE ForEachInsertedRowTriggerCursor            */

        /* 
        *   SSMA error messages:
        *   O2SS0174: The declaration of the identifier 'ForEachInsertedRowTriggerCursor' was converted with error(s).

        DEALLOCATE ForEachInsertedRowTriggerCursor            */
     END

The problems, in brief, are that

1. after turning off ROWID column generation it DID NOT create any ROWID column in any table which is exactly required

2. But it did create all triggers with as if ROWID column was there in table.

3. And yes it generated trigger with commenting all queries that was expecting a ROWID ... (as you can see from sample stored procedure)

Is there any other way/option we have to Turn off the generation of ROWID from triggers too ?

回答1:

Your trigger in Oracle is FOR EACH ROW. This type of triggers is not directly supported by SQL Server. So SSMA applies a template replacement for them using INSTEAD OF trigger and loop over inserted.

Can you enable ROWID at least for tables with triggers (option "Add ROWID column for tables with triggers" in Conversion settings)? SSMA doesn't support converting this type of triggers automatically without it, you may have to modify converted triggers by hand otherwise. (That's why it still tries to convert as much as it can but leaves access to missing ROWID column commented out - so you can finish conversion manually).