Migration of triggers is one of the most important steps of moving database to new DBMS. At the same time this procedure can hardly be automated due to essential difference in creating triggers in SQL Server and MySQL. This article covers some basic aspects of migrating MS SQL triggers to MySQL. It is expected that target audience has some experience in database programming.
There are some notable differences between trigger syntax in SQL Server and MySQL (and other databases in general).
First, MySQL references old (existing) and new (inserted or updated) data by NEW.column_name and OLD.column_name. In SQL Server those values are stored in two service tables named “INSERTED” and “DELETED”, it is required to do the appropriate selects in order to access the data.
Another difference is SQL Server allows single trigger to handle multiple actions (insert/update/delete). In MySQL such triggers must be duplicated for each action.
Finally, SQL Server iterates all new/old as reading from table with a cursor. For the same purpose MySQL uses implicit loop through declaration “FOR EACH ROW”.Let us illustrate these differences via example of converting trigger from MS SQL to MySQL. Assume there is trigger that converts a string into lowercase before inserting defined in SQL Server as follows:
CREATE TRIGGER make_it_lower ON customers instead of INSERT AS
INSERT INTO customers(code) SELECT LOWER(code) FROM INSERTED;The same trigger in MySQL should look like this:
DROP TRIGGER make_it_lower;
DELIMITER $$
CREATE TRIGGER make_it_lower before INSERT ON customers FOR EACH ROW
BEGIN
SET NEW.code = LOWER(NEW.code);
END$$
DELIMITER ;Database object names may be enclosed in square brackets in SQL Server code. MySQL does not accept such syntax; all square brackets must be replaced by ` symbol or cut off: [object] -> `object`. Also, MS SQL supports schemas, a virtual container grouping and categorizing objects inside the single database by ownership or similar semantics. Due to this fact full name of database object may look like database.schema.object. MySQL does not support schemas, so all schema names must be removed from the source code.
Finally, all embedded MS SQL functions must be replaced by MySQL equivalents according this table:
| SQL Server | MySQL |
| CONVERT($type, $expression) | CAST($expression AS $type) |
| 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) |
To learn more about other aspects of SQL Server to MySQL migration visit this page: https://www.convert-in.com/docs/mss2sql/intro.htm