即使在SSMA v 6.0中将其生成关闭，也会在触发器中生成ROWID列

问题描述

我使用SSMA v6.0将Oracle数据库迁移到SQL Server 2014.

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

我关闭了ROWID列的生成，并且按预期，它在转换后没有在我的任何表中生成任何其他ROWID列，但是令人惊讶的是，它DID生成了与各自表相关联的所有触发器，并在ROWID列中注入了触发器，例如以下:

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

简而言之，问题在于

1. 在关闭ROWID列生成后，它没有在完全需要的任何表中创建任何ROWID列

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

但是它确实创建了所有触发器，就像表中的ROWID列一样.

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

是的，它生成了注释为ROWID的所有查询的触发器……(如您从示例存储过程中看到的那样)

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

我们还有其他方法/选项来关闭触发器的ROWID生成吗?

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

推荐答案

您在Oracle中的触发器是FOR EACH ROW. SQL Server不直接支持这种类型的触发器.因此，SSMA使用INSTEAD OF触发器并在inserted上循环对其应用模板替换.

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.

您是否至少可以为具有触发器的表启用ROWID(转换设置中的选项为具有触发器的表添加ROWID列")?如果没有SSMA，SSMA不支持自动转换这种类型的触发器，否则您可能必须手动修改转换后的触发器. (这就是为什么它仍然尝试尽可能多地进行转换，但是将对缺少的ROWID列的访问权限注释掉了-因此您可以手动完成转换.)

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).

这篇关于即使在SSMA v 6.0中将其生成关闭，也会在触发器中生成ROWID列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！