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

In SQL Server, inserted is a system table available in a statement-level trigger that holds all inserted or updated rows. In MariaDB 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 MariaDB, you have to use a row-level trigger as follows:

MariaDB:

  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 MariaDB, 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.

For more information, see SQL Server to MariaDB Migration.