0

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: enter image description here

2
  • The error message seems pretty self-explanatory. You should be using BEFORE triggers, or, rather, a BEFORE UPDATE trigger, since you can simply use DEFAULT or GENERATED values upon insert.
    – mustaccio
    Mar 2 at 19:04
  • @mustaccio I have more experience in MS SQL and there I could use the AFTER keyword. At first I thought my datetime field was the problem, because I got errors from that one too at the beginning. Mar 2 at 21:09

1 Answer 1

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.

  1. 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.

3
  • Thank very much. I come up with this: CREATE DEFINER=root@localhost` TRIGGER userlogtype_BEFORE_INSERT BEFORE INSERT ON userlogtype FOR EACH ROW BEGIN IF NEW.InsertDateTime IS NULL THEN SET NEW.InsertDateTime = now(6); END IF; IF NEW.UpdateDateTime IS NULL THEN SET NEW.UpdateDateTime = now(6); END IF; IF NEW.InsertUser IS NULL THEN SET NEW.InsertUser = current_user(); END IF; IF NEW.UpdateUser IS NULL THEN SET NEW.UpdateUser = current_user(); END IF; END` Mar 2 at 21:12
  • and CREATE DEFINER=root@localhost` TRIGGER userlogtype_BEFORE_UPDATE BEFORE UPDATE ON userlogtype FOR EACH ROW BEGIN SET NEW.UpdateDateTime = now(6); SET NEW.UpdateUser = current_user(); END` Mar 2 at 21:13
  • but you solution is more elegant - ps. I added the code in the comment to help others too Mar 2 at 21:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.