When migrating from one database management system to another, migration of triggers is as important as well. Migration of database from MS SQL to MySQL is one of such. The probability of automating this procedure is difficult because of the different in the creation of triggers in the different databases. The purpose of this article is to highlight the processes involved in migration of triggers from MS SQL to MySQL. However, you may not understand the content of this article if you lack the basic knowledge of database programming.
There are significant differences when it comes to trigger syntax in SQL server and MySQL. This also applies to other databases with similar syntax.
An important feature of MySQL that you must understand is that there are appropriate references for data types such as:
- Old (existing) – OLD.column_name
- New (inserted or updated) – NEW.column_name
Both values are stored in SQL server in two service tables namely ‘DELETED’ and ‘INSERTED’. To access the data, you have to do the appropriate selects.
Also, single trigger in SQL server can handle multiple actions which are insert, update, and delete. For each action, triggers in MySQL have to be duplicated.
By using a cursor, SQL server can iterate both new and old ad ‘reading from table’. Similarly, the declaration ‘FOR EACH ROW’ is the channel through which MySQL uses an implicit loop.
All three differences can be illustrated. This will be shown by stating examples of the conversion of triggers from MS SQL to MySQL. For this purpose, we shall assume we have a trigger which inserts ‘defined’ in SQL server after converting a string into lowercase. By practice, this is:
CREATE TRIGGER make_it_lower ON customers instead of INSERT AS
INSERT INTO customers(code) SELECT LOWER(code) FROM INSERTED;
In MySQL, this trigger takes another form such as:
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 ;
There are some entities called square brackets in the SQL Server code which are used to enclose database objects. But in MySQL, this syntax is not allowed. Instead, all square brackets have to be replaced by ` symbol or cut off: [object] -> `object`.
In addition, MS SQL has a system support for schemas. Schemas are virtual container grouping which, by similar semantics or ownership, categorize objects within a single database. Owing to this, the full name of database object is put in writing as database.schema.object. In MySQL, all schema names have to be removed from the source code because of its inability to support schemas.
The table below shows how embedded MS SQL functions can be replaced by MySQL equivalents:
| 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) |
This whitepaper explores just generic rules of triggers migration from SQL Server to MySQL. Contact to Intelligent Converters software company to get complete turnkey solution on any kind of database migration projects.
