MySQL Forums
Forum List  »  Triggers

Re: inserting actual username of a person who edited record into a table with trigger MySQL
Posted by: Maciej Domanski
Date: October 04, 2017 09:41AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: inserting actual username of a person who edited record into a table with trigger MySQL
322
October 04, 2017 09:41AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.