locked
SSMA conversion of ON UPDATE EMBEDDED TRIGGER RRS feed

  • Question

  • Some features of MYSQL like (ON UPDATE CURRENT_TIMESTAMP) for a column is missing during conversion.
    On this example column C3 during conversion will loose the ( ON UPDATE CURRENT_TIMESTAMP)
    SSMA did not create any trigger for this part of the code either.

    MY Sql 5.7
    Sql Server 2012
    SSMA 7.3

    CREATE TABLE `ssmatest2` (
      `C1` int(11) NOT NULL,
      `C2` varchar(255) DEFAULT NULL,  
      `C3` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB;
    Friday, May 26, 2017 9:51 AM

All replies

  • Hi Mike,

    Based on my test, it seems SSMA doesn’t have the capability to do it automatically at this moment. I’d suggest you create your own trigger in SQL server. For example:
    CREATE TRIGGER test.Trigger_timestamp1 
       ON  test.ssmatest2 
       FOR UPDATE
    AS 
    BEGIN
    
        IF UPDATE(C3)
    RETURN;
    ELSE
     UPDATE f set C3=GETDATE() 
      FROM
            test.ssmatest2  AS f
            INNER JOIN inserted 
      AS i
            ON f.C3 = i.C3;
    END
    GO



    Furthermore, with SSMA 7.3 and later, it’s possible for you to create your own converter and save it for further use. For more information, please review this video.

    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.

    Monday, May 29, 2017 8:21 AM
  • That is true, we can develop our code for missing piece. But the main concern is SSMA at the current behavior dismisses the syntax by not generating any WARNING or ERROR. as the result they are lost altogether and user will not know about such missing elements.

    My suggestion: as such code is part of schema and data structure, system should generate ERROR for such lines.

    Currently: I exported the entire source script to a file and searched for such notation among all tables and modify the code in target database.

    Thursday, June 1, 2017 9:53 AM
  • Hi Mike,

    >>But the main concern is SSMA at the current behavior dismisses the syntax by not generating any WARNING or ERROR.

    I agree with you, at least user should receive a warning about that. In this case, I’d suggest you submit a feedback to Microsoft directly at this site. This connect site will serve as a connecting point between you and Microsoft, and ultimately the large community for you and Microsoft to interact with. Your feedback enables Microsoft to offer the best software and deliver superior services, meanwhile you can learn more about and contribute to the exciting projects on Microsoft Connect.

    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.


    Thursday, June 1, 2017 9:59 AM