Migrating triggers from SQL Server to MySQL

Being important part of database, triggers require special attention during migration to a new DBMS. Migration of triggers from SQL Server to MySQL can hardly be automated due to essential difference between syntax of triggers in these two DBMS. This whitepaper explores some common steps of migrating triggers from SQL Server to MySQL. The target audience is supposed to have at least basic experience in database administration.

There are valuable differences between syntax of CREATE TRIGGER statements in SQL Server and MySQL. In SQL Server old (existing) and new (inserted or updated) data is stored in special tables named INSERTED and DELETED. On the other hand, MySQL references those values by NEW.column_name and OLD.column_name.

In SQL Server single trigger can handle multiple actions: insert or update or delete. In MySQL code of such trigger must be duplicated for each action since DBMS does not allow declarations of multiple actions for the single trigger.

SQL Server triggers iterate all new/old rows through reading from INSERTED or DELETED table via 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 

For the same purpose MySQL uses implicit FOR EACH ROW loop declared like this:

CREATE TRIGGER UpdateAssignedReadingPhysicianID2`
AFTER UPDATE ON  ReportDocuments FOR EACH ROW
BEGIN
	IF(NEW.ReadingPhysicianID <> OLD.ReadingPhysicianID) THEN
		UPDATE Study SET Study.AssignedReadingPhysicianID = NEW.ReadingPhysicianID;
...

In SQL Server database object names may be escaped in square brackets []. In MySQL all square brackets must be replaced by backtick ` symbol or cut off: [object] -> `object`. Also, SQL Server supports schemas, a kind of container to group database objects by ownership or similar semantic and so the full name of may look like database.schema.object. Since MySQL does not support schemas, all schema names must be removed from the trigger's source code.

The following example of migrating trigger from SQL Server to MySQL illustrates the syntax differences mentioned above. Assume there is trigger that converts a string into lowercase before inserting defined in SQL Server 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 it is specified in the article How to convert queries/views.

Have any questions? Contact us