Re: inserting actual username of a person who edited record into a table with trigger MySQL

[Maciej Domanski]

I solved the problem myself, the source of it was the wrong order of the fields in the INSERT INTO part of the triggers.
Finally the triggers should look like:

DELIMITER $$

CREATE TRIGGER contacts_after_update
AFTER UPDATE
ON `CAPI-TEST`.`addresses` FOR EACH ROW
BEGIN
DECLARE `username` varchar(60);
SELECT SUBSTRING_INDEX(USER(),'@',1) INTO `username`;
INSERT INTO `addresses_do-not-use` (`action`, `username`, `revision`, `dt_datetime`, `AddressID`, `HHNO`, `NicolaID`, `PropertyName`, `PropertySubName`, `PropertyNumber`, `PropertyStreet`, `PropertyTown`, `PropertyCounty`, `Postcode`, `ADD1`, `ADD2`, `ADD3`, `ADD4`, `Number_of_eligible_participants`) SELECT 'update', `username`, NULL, NOW(), d.*
FROM `addresses` AS d WHERE d.AddressID = NEW.AddressID;
END;

DELIMITER ;

^^^
above is the after update trigger