MySQL Forums
Forum List  »  Triggers

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

table addresses:

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',
PRIMARY KEY (`AddressID`),
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;




Table addresses_do-not-use:

CREATE TABLE `addresses_do-not-use` LIKE `addresses`;

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 username VARCHAR(60) AFTER action,
ADD revision INT(6) NOT NULL AUTO_INCREMENT AFTER username,
ADD dt_datetime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER revision,
ADD PRIMARY KEY (AddressID, 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.*
FROM `addresses` AS d WHERE d.AddressID = NEW.AddressID

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.*
FROM `addresses` AS d WHERE d.AddressID = NEW.AddressID

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.*
FROM `addresses` AS d WHERE d.AddressID = OLD.AddressID



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

Options: ReplyQuote


Subject
Views
Written By
Posted
inserting actual username of a person who edited record into a table with trigger MySQL
1279
October 04, 2017 07:02AM


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.