How to Migrate Triggers from SQL Server to MySQL

When migrating from SQL Server to MySQL, handling triggers properly is extremely important since it is essential part of the database logic. There is a little chance of automating this conversion because syntax of triggers creation is different in those two DBMS. The purpose of this article is to highlight the processes involved in migration of triggers from SQL Server to MySQL. It is supposed that audience has the basic knowledge of database programming.

First significant difference of trigger definitions in SQL Server and MySQL is the way of referencing old and new data. 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. This rule can be illustrated by the following case. Assume, there is a MS SQL trigger:

CREATE TRIGGER [dbo].[update_archive] ON [dbo].[archive]

INSTEAD OF INSERT

UPDATE dbo.archive

SET archive.AssignedReadingPhysicianID = 

INSERTED.ReadingPhysicianID

FROM dbo.archive INNER JOIN INSERTED ON archive.StudyUID = 

INSERTED.StudyUID

WHERE archive.ReportCount = 0

It can be migrated to MySQL as follows:

CREATE TRIGGER update_archive

AFTER UPDATE ON archive FOR EACH ROW

BEGIN

IF(NEW.ReadingPhysicianID <> OLD.ReadingPhysicianID) THEN

UPDATE archive SET archive.AssignedReadingPhysicianID = NEW.ReadingPhysicianID;

...

END$$

DELIMITER ;

Also, single trigger in SQL Server can handle multiple actions which are insert, update, and delete. MySQL does not have such capability, so triggers must be duplicated for each action. 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 SQL Server 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 to_lowercase ON journal instead of INSERT AS

INSERT INTO journal(code) SELECT LOWER(code) FROM INSERTED;

In MySQL, this trigger takes another form such as:

DROP TRIGGER to_lowercase;

DELIMITER $$  

CREATE TRIGGER to_lowercase before INSERT ON journal 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 to convert embedded from SQL Server to MySQL equivalents: