After Insert and Update Trigger on the same table

Question

I have the following table in MySQL:

CREATE TABLE `userlogtype` (
  `UserLogTypeID` int NOT NULL AUTO_INCREMENT,
  `UserLogType` varchar(150) DEFAULT NULL,
  `InsertUser` varchar(100) DEFAULT NULL,
  `InsertDateTime` datetime DEFAULT '0000-00-00 00:00:00',
  `UpdateUser` varchar(100) DEFAULT NULL,
  `UpdateDateTime` datetime DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`UserLogTypeID`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

and I would like to create 2x triggers: one after insert and one after update.

1. After Insert I would like to update the new row: InsertUser + InsertDateTime fields
2. After Update I would like to update the new row: UpdateUser + UpdateDateTime fields

I tried this:

CREATE DEFINER=`root`@`localhost` TRIGGER `userlogtype_AFTER_INSERT` AFTER INSERT ON `userlogtype` FOR EACH ROW BEGIN
    UPDATE userlogtype
    SET InsertUser = CASE WHEN InsertUser IS NULL OR InsertUser='' OR InsertUser=' ' THEN CURRENT_USER ELSE InsertUser END,
    InsertDateTime = CASE WHEN InsertDateTime IS NULL OR InsertDateTime='0000-00-00 00:00:00' OR InsertDateTime=0  THEN CURRENT_TIMESTAMP ELSE InsertDateTime END
    WHERE UserLogTypeID=NEW.UserLogTypeID ; 
END

I can save the trigger without an error in MySQL Workbench, but when I test an insert (inserting just a value for UserLogType), it gives the following error:

Operation failed: There was an error while applying the SQL script to the database. Executing: INSERT INTO artistabookingdb.userlogtype (UserLogType) VALUES ('erty');

ERROR 1442: 1442: Can't update table 'userlogtype' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. SQL Statement: INSERT INTO artistabookingdb.userlogtype (UserLogType) VALUES ('erty')

It's a bit strange that the default values not appear when I try to insert a value to the UserLogType field:

Answer 1

1. Alter the table structure:

CREATE TABLE `userlogtype` (
  `UserLogTypeID` int NOT NULL AUTO_INCREMENT,
  `UserLogType` varchar(150) DEFAULT NULL,
  `InsertUser` varchar(100) DEFAULT NULL,
  `InsertDateTime` datetime DEFAULT CURRENT_TIMESTAMP,
  `UpdateUser` varchar(100) DEFAULT NULL,
  `UpdateDateTime` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`UserLogTypeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Now the values in InsertDateTime and UpdateDateTime will be set automatically.

2. Create BEFORE (not AFTER !) triggers which will assign current user into according column if the value is not specified in the query:

CREATE TRIGGER `tr_bi_userlogtype` 
BEFORE INSERT ON `userlogtype` 
FOR EACH ROW 
SET NEW.InsertUser = COALESCE(NULLIF(TRIM(NEW.InsertUser), ''), CURRENT_USER);

CREATE TRIGGER `tr_bu_userlogtype` 
BEFORE UPDATE ON `userlogtype` 
FOR EACH ROW 
SET NEW.UpdateUser = COALESCE(NULLIF(TRIM(NEW.UpdateUser), ''), CURRENT_USER);

TRIM() removes space chars. NULLIF() compares trimmed value with empty string, and returns NULL if it is equal. COALESCE() returns specified value or CURRENT_USER if the value is not set.