locked
O2SS0050: Conversion of identifier 'INSERTING' is not supported. RRS feed

  • Question

  • I'm performing a Oracle to SQL migration test using the SSMA tool version 7.4

    In a certain trigger i get the message that converting the keyword 'inserting' is not supported.

               
    BEFORE INSERT OR UPDATE ON DMI.MDIAG_ICD10
    
    REFERENCING NEW AS NEW OLD AS OLD
    
    FOR EACH ROW
    
    BEGIN
    
        IF (INSERTING) THEN
    
    Do something
    
        ELSIF (UPDATING) THEN
    
    Do something else
    
        END IF;
    
    END;

    * SSMA error messages:
    * O2SS0050: Conversion of identifier 'INSERTING' is not supported.

    IF ((NULL) != 0)



    I would like to find a way to tell the SSMA to replace this one trigger with two (one for insert and one for update).

    They would look something like this:        

    CREATE OR REPLACE TRIGGER "DMI"."MDIAG_ICD10_I" 
    
    BEFORE INSERT ON DMI.MDIAG_ICD10
    
    REFERENCING NEW AS NEW OLD AS OLD
    
    FOR EACH ROW
    
    BEGIN
    
    
    
          :new.dte_cre := sysdate;
    
          :new.usr_cre := NP.PUTIL.GET_USERSPER(user);
    
    
    END;
    
    
    CREATE OR REPLACE TRIGGER "DMI"."MDIAG_ICD10_U"
    BEFORE UPDATE ON DMI.MDIAG_ICD10
    
    REFERENCING NEW AS NEW OLD AS OLD
    
    FOR EACH ROW
    
    BEGIN
    
          :new.dte_mod := sysdate;
    
          :new.usr_mod := NP.PUTIL.GET_USERSPER(user);
    
    
    
           INSERT INTO npzh.hMDIAG_ICD10(MSEJ,SICD10_UREG,USR_CRE,USR_MOD,DTE_CRE,DTE_MOD, rlo_del) VALUES (:OLD.MSEJ,:OLD.SICD10_UREG,:OLD.USR_CRE,:OLD.USR_MOD,:OLD.DTE_CRE,:OLD.DTE_MOD, 'N');
    END;

    When I make a report from this the whole triggerfile is just marked as an error:

       SSMA error messages:
     * O2SS0004: Unparsed SQL (Parse error at line 21, column 1. Encountered: CREATE) [CREATE OR REPLACE TRIGGER "DMI"."MDIAG_ICD10_I"

    Because it finds two create statements. Can anyone please give me a way to fix this? 

    Thanks


    • Edited by ThomasVdz Tuesday, July 4, 2017 2:12 PM
    Tuesday, July 4, 2017 2:06 PM

Answers

  • Hi Thomas,

    I don’t have the full answer yet, but:

    >>REFERENCING NEW AS NEW OLD AS OLD

    If I understand this correctly, it’s Oracle way of setting table alias. In SQL Server we utilize AS statement for this.

    >>FOR EACH ROW

    I cannot find the corresponding statement in SQL Server, but since we have inserted and deleted logical table, you might need to use a cursor on inserted/deleted table.

    >>BEFORE INSERT OR UPDATE ON DMI.MDIAG_ICD10

    Currently SQL Server does not have before trigger, you ‘d have to user AFTER or INSTEAD OF instead.

    I’ve also found a related blog for your reference.

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 5, 2017 9:13 AM

All replies

  • Hi Thomas,

    I don’t have the full answer yet, but:

    >>REFERENCING NEW AS NEW OLD AS OLD

    If I understand this correctly, it’s Oracle way of setting table alias. In SQL Server we utilize AS statement for this.

    >>FOR EACH ROW

    I cannot find the corresponding statement in SQL Server, but since we have inserted and deleted logical table, you might need to use a cursor on inserted/deleted table.

    >>BEFORE INSERT OR UPDATE ON DMI.MDIAG_ICD10

    Currently SQL Server does not have before trigger, you ‘d have to user AFTER or INSTEAD OF instead.

    I’ve also found a related blog for your reference.

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 5, 2017 9:13 AM
  • Hi,

    SSMA 7.5 should be able to convert INSERTING and UPDATING special functions and you don't need to separate one trigger into multiple.

    Regards,
    Alex.

    Monday, February 26, 2018 4:35 PM