MySQL Forums

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

I have a problem with inserting current user who actually insert,update or delete the data into the table.
I know that the subject was described many times and I did a lot of research, yet ended up here asking for help with this problem.

I have 2 tables: addresses and addresses_do-not-use

CREATE TABLE `addresses` (
`AddressID` INT(11) NOT NULL AUTO_INCREMENT,
`HHNO` VARCHAR(10) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`NicolaID` VARCHAR(10) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`PropertyName` VARCHAR(60) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`PropertySubName` VARCHAR(60) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`PropertyNumber` VARCHAR(10) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`PropertyStreet` VARCHAR(60) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`PropertyTown` VARCHAR(60) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`PropertyCounty` VARCHAR(60) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`Postcode` VARCHAR(10) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`ADD1` VARCHAR(128) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`ADD2` VARCHAR(128) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`ADD3` VARCHAR(128) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`ADD4` VARCHAR(128) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`Number_of_eligible_participants` VARCHAR(5) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
INDEX `NicolaID` (`NicolaID`) USING BTREE,
CONSTRAINT `addresses_ibfk_1` FOREIGN KEY (`NicolaID`) REFERENCES `ids` (`NicolaID`) ON UPDATE CASCADE ON DELETE CASCADE
) COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=8539;

ALTER TABLE `addresses_do-not-use` MODIFY COLUMN AddressID INT(11) NOT NULL,
DROP PRIMARY KEY, ENGINE = MyISAM,
ADD action VARCHAR(8) DEFAULT 'insert' FIRST,
ADD revision INT(6) NOT NULL AUTO_INCREMENT AFTER username,
ADD dt_datetime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER revision,

then I created triggers.
I added USER function in order to collect the data who changed the record.

DROP TRIGGER IF EXISTS `CAPI-TEST`.`addresses_ai`;
DROP TRIGGER IF EXISTS `CAPI-TEST`.`addresses_au`;
DROP TRIGGER IF EXISTS `CAPI-TEST`.`addresses_bd`;

CREATE TRIGGER `CAPI-TEST`.`addresses_ai` AFTER INSERT ON `CAPI-TEST`.`addresses` FOR EACH ROW
INSERT INTO `addresses_do-not-use` (`action`, `revision`, `username`, `dt_datetime`, `AddressID`, `HHNO`, `NicolaID`, `PropertyName`, `PropertySubName`, `PropertyNumber`, `PropertyStreet`, `PropertyTown`, `PropertyCounty`, `Postcode`, `ADD1`, `ADD2`, `ADD3`, `ADD4`, `Number_of_eligible_participants`) SELECT 'insert', SUBSTRING_INDEX(USER(),'@',1), NULL, NOW(), d.*

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

CREATE TRIGGER `CAPI-TEST`.`addresses_bd` BEFORE DELETE ON `CAPI-TEST`.`addresses` FOR EACH ROW
INSERT INTO `addresses_do-not-use` (`action`, `revision`, `username`, `dt_datetime`, `AddressID`, `HHNO`, `NicolaID`, `PropertyName`, `PropertySubName`, `PropertyNumber`, `PropertyStreet`, `PropertyTown`, `PropertyCounty`, `Postcode`, `ADD1`, `ADD2`, `ADD3`, `ADD4`, `Number_of_eligible_participants`) SELECT 'delete', SUBSTRING_INDEX(USER(),'@',1), NULL, NOW(), d.*

The username field remains empty in the addresses_do-not-use table after amending records in addresses table. The rest of the fields are OK but not username. The MySQL version is 5.1.73.
Users connect using HeidiSQL/Navicat being logged in as themselves, so their username should be passed without any problems.

Any ideas why?

I would really appreciate any help with this.

Maciej

Subject
Views
Written By
Posted
inserting actual username of a person who edited record into a table with trigger MySQL
3782
October 04, 2017 07:02AM
1109
October 04, 2017 09:12AM
701
October 04, 2017 10:29AM
648
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.