4753

Generation of ROWID column in triggers even when its generation is switched off in SSMA v 6.0

Question:

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

<ol><li>

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

</li> <li>

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

</li> <li>

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

</li> </ol>

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

Answer1:

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

Recommend