inserted - System Table in Trigger - SQL Server to MySQL Migration

In SQL Server, inserted is a system table available in a statement-level trigger that holds all inserted or updated rows. In MySQL you have to use a row-level trigger and the direct column reference using the NEW column prefix.

Consider a sample table with a trigger:

SQL Server:

  CREATE TABLE colors (name VARCHAR(30), ln INT);
 
  -- Sample trigger that outputs the number of inserted rows
  CREATE OR ALTER TRIGGER colors_tr ON colors AFTER INSERT
  AS
     DECLARE @ln INT;
     SELECT @ln = COUNT(*) FROM inserted;
     PRINT 'Inserted rows: ' + STR(@ln)
  GO

Now if we insert rows into the table, we can see the number of inserted rows:

SQL Server:

  -- Insert 1 row
  INSERT INTO colors(name) VALUES ('Green');
  # Inserted rows:          1
 
  -- Insert 2 rows
  INSERT INTO colors(name) VALUES ('Red'), ('Black');
  # Inserted rows:          2

Consider an example when we need to update columns of the inserted rows in the trigger:

SQL Server:

  -- Trigger that updates columns of inserted rows
  CREATE OR ALTER TRIGGER colors_tr ON colors AFTER INSERT
  AS
    UPDATE t
    SET t.ln = CASE 
        WHEN ins.ln IS NULL OR ins.ln = 0 THEN len(ins.name) 
        ELSE ins.ln 
    END
    FROM colors t INNER JOIN inserted ins ON t.name = ins.name;
  GO

Now if we insert a new row, the ln column is updated with the name length:

  -- Trigger will set the value for ln column
  INSERT INTO colors(name) VALUES ('White');
 
  SELECT ln FROM colors WHERE name = 'White';
  # 5

In MySQL, you have to use a row-level trigger as follows:

MySQL:

  DELIMITER //
 
  CREATE TRIGGER colors_tr BEFORE INSERT ON colors
  FOR EACH ROW
  BEGIN
     SET NEW.ln = CASE 
       WHEN NEW.ln IS NULL OR NEW.ln = 0 THEN char_length(NEW.name) 
       ELSE NEW.ln 
     END;
  END;
  //
 
  DELIMITER ;

Note that since we modify the table columns we have to change the trigger to BEFORE trigger, otherwise we will get the error:

  # ERROR 1362 (HY000): Updating of NEW row is not allowed in after trigger

Also note that in MySQL, you cannot update the triggered table using UPDATE statement, and if you try use the following statement to assign the ln values, the trigger will be successfully created but fail on inserting rows:

   -- Using UPDATE for the trigger table will fail on INSERT
   UPDATE colors t 
     SET t.ln = CASE 
       WHEN NEW.ln IS NULL OR NEW.ln = 0 THEN char_length(NEW.name) 
       ELSE NEW.ln 
     END;

Then if you execute an insert:

  INSERT INTO colors(name) VALUES ('White');
  # ERROR 1442 (HY000): Can't update table 'colors' in stored function/trigger because 
  # it is already used by statement which invoked this stored function/trigger.

Although inserted table can hold multiple rows, it is typical to assume that a trigger is applied for single row inserts, and use SELECT statements to assign values to local variables, for example:

SQL Server:

  -- Trigger that selects columns from inserted rows
  CREATE OR ALTER TRIGGER colors_tr ON colors AFTER INSERT
  AS
    DECLARE @name VARCHAR(30);
    DECLARE @ln INT;
 
    -- Select 1 row
    SELECT @name = name, @ln = ln FROM inserted;
 
    -- Select with a condition
    SELECT @name = name, @ln = ln 
    FROM inserted
    WHERE ISNULL(ln, 0) > 0 AND name IS NOT NULL;
 
    -- Select with a join
    SELECT @name = inserted.name, @ln = ln 
    FROM inserted 
      LEFT JOIN category cat ON inserted.name = cat.name
      LEFT JOIN category2 cat2 ON cat.name = cat2.name;
 
    -- Some other logic here...
  GO

In MySQL, you can still use the AFTER trigger as the inserted values are not changed by SELECT, and use the NEW prefix for columns instead of inserted table:

MySQL:

  DROP TRIGGER IF EXISTS colors_tr;
 
  DELIMITER //
 
  -- Trigger that selects columns from inserted rows
  CREATE TRIGGER colors_tr AFTER INSERT ON colors
  FOR EACH ROW
  BEGIN
    DECLARE v_name VARCHAR(30);
    DECLARE v_ln INT;
 
    -- Select 1 row
    SELECT NEW.name, NEW.ln INTO v_name, v_ln;
 
    -- Select with a condition
    IF IFNULL(NEW.ln, 0) > 0 AND NEW.name IS NOT NULL 
    THEN 
      SELECT NEW.name, NEW.ln INTO v_name, v_ln; 
    END IF;
 
    -- Select with a join
    SELECT NEW.name, NEW.ln INTO v_name, v_ln 
    FROM category cat
      LEFT JOIN category2 cat2 ON cat.name = cat2.name;
  END;
  //
 
  DELIMITER ;

For more information, see SQL Server to MySQL Migration.