Skip to main content
Database Administrators

Return to Revisions

2 of 3
added 304 characters in body
domaniqs
domaniqs
  • 3
  • 4

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

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) NOT NULL DEFAULT USER() 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 following this link: Is there a MySQL option/feature to track history of changes to records? I added USER function in order to collect the data who changed the record.

    DROP TRIGGER IF EXISTS `addresses_ai`;
    DROP TRIGGER IF EXISTS `addresses_au`;
    DROP TRIGGER IF EXISTS `addresses_bd`;

CREATE TRIGGER CAPI-TEST.addresses_do-not-use__ai AFTER INSERT ON CAPI-TEST.addresses_do-not-use 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_do-not-use__au AFTER UPDATE ON CAPI-TEST.addresses_do-not-use 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_do-not-use__bd BEFORE DELETE ON CAPI-TEST.addresses_do-not-use 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

domaniqs
  • 3
  • 4