As a crucial component of databases, triggers demand particular consideration when undergoing migration to a new DBMS. The process of transferring triggers from SQL Server to MySQL cannot be easily automated due to fundamental differences in trigger syntax between these two database management systems. This whitepaper delves into several typical steps involved in migrating triggers from SQL Server to MySQL. The published materials require basic level of experience in database administration.
Significant distinctions exist in the syntax of the CREATE TRIGGER statements when comparing SQL Server and MySQL. In SQL Server, previous (existing) and new (inserted or updated) data finds its place within dedicated tables labeled as “INSERTED” and “DELETED.” In contrast, MySQL makes use of references like NEW.column_name and OLD.column_name to access these values.
In SQL Server, a solitary trigger can manage various actions: insertion, updating, or deletion. However, in MySQL, the code for such a trigger must be replicated for each individual action. This necessity arises because the DBMS does not permit the declaration of multiple actions within a single trigger.
To access new/old data, SQL Server triggers iterate through special tables “INSERTED” and “DELETED” using cursor or joining those tables into DML queries, for example:
UPDATE dbo.Study
SET Study.AssignedReadingPhysicianID = INSERTED.ReadingPhysicianID
FROM dbo.Study INNER JOIN INSERTED ON Study.StudyUID = INSERTED.StudyUID
WHERE Study.ReportCount = 0
MySQL uses implicit “FOR EACH ROW” loop for the same purpose, here is an example of that cycle declaration:
CREATE TRIGGER UpdateAssignedReadingPhysicianID2`
AFTER UPDATE ON ReportDocuments FOR EACH ROW
BEGIN
IF(NEW.ReadingPhysicianID <> OLD.ReadingPhysicianID)
THEN
UPDATE Study SET Study.AssignedReadingPhysicianID = NEW.ReadingPhysicianID;
Within SQL Server databases, object names can be enclosed in square brackets []. However, in MySQL, these square brackets must either be replaced with backtick symbols or omitted entirely: [object] becomesobject`. Additionally, SQL Server includes support for schemas, which act as containers for grouping database objects based on ownership or related semantics. As a result, a complete object name might appear as database.schema.object. Nevertheless, since MySQL lacks support for schemas, all schema names must be removed from the source code of triggers.
To illustrate the aforementioned syntax differences, consider the following example of migrating a trigger from SQL Server to MySQL. Suppose there’s a trigger in SQL Server that converts a string into lowercase before performing an insertion. The trigger is defined as follows:
CREATE TRIGGER [dbo].[make_it_lower] ON [dbo].[customers]
INSTEAD OF INSERT
AS
INSERT INTO [dbo].[customers](code) SELECT LOWER(code) FROM INSERTED;
The same trigger in MySQL should be composed like this:
DELIMITER $$
CREATE TRIGGER make_it_lower before INSERT ON customers FOR EACH ROW
BEGIN
SET NEW.code = LOWER(NEW.code);
END$$
DELIMITER ;
Finally, all built-in functions of SQL Server must be replaced by MySQL equivalents as follows:
| SQL Server | MySQL |
| LEN($expression) | LENGTH($expression) |
| DATEADD(year, 1, $expression) | $expression + interval 1 year |
| DATEADD(month, 2, $expression) | $expression + interval 2 month |
| DATEADD(day, 3, $expression) | $expression + interval 3 day |
| GETDATE() | NOW() |
| ‘string1’ + ‘string2’ | CONCAT(‘string1’, ‘string2’) |
| CONTAINS($expression, $template) | $expression LIKE $template |
| CHARINDEX ($exp1, $exp2) | LOCATE($exp1, $exp2) |
During migration of T-SQL code from SQL Server to MySQL function CONVERT requires special attention as it may serve for different purposes depending on its arguments.
In SQL Server, the CONVERT(DATETIME, string, format) function is available to transform strings into DATETIME values based on the specified format. On the other hand, MySQL, MariaDB, and Percona provide the STR_TO_DATE(string, format) function for achieving the same objective.
The table presented below demonstrates the process of substituting SQL Server format specifications with their corresponding equivalents in MySQL:
| SQL Server | MySQL | Comments |
| 101 | %m/%d/%Y | U.S. default (mm/dd/yyyy) |
| 102 | %Y.%m.%d | ANSI format (yyyy.mm.dd) |
| 103 | %d/%m/%Y | British/French (dd/mm/yyyy) |
| 104 | %d.%m.%Y | German (dd.mm.yyyy) |
| 105 | %d-%m-%Y | Italian (dd-mm-yyyy) |
| 110 | %m-%d-%Y | USA format (mm-dd-yyyy) |
| 111 | %Y/%m/%d | Japan format (yyyy/mm/dd) |
| 112 | %Y%m%d | ISO format (yyyymmdd) |
| 120 | %Y-%m-%d %H-%i-%s | ODBC (yyyy-mm-dd hh24:mi:ss) |
| 121 | %Y-%m-%d %T.%f | ODBC with milliseconds |
In case you find migration of triggers from SQL Server to MySQL too complicated, it is reasonable to use special tools that can completely of partially automate this procedure. One of such tools is SQL Server to MySQL Code Converter developed by Intelligent Converters software company being focused on database migration and synchronization since 2001.
