0

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 `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

Improve this question

1 Answer 1

Reset to default
0

I found the problem: everything works out right now. The problem was the order of the fields in trigger, copy-paste technique while creating them made problem unseen until test stage. The triggers should look like:

CREATE TRIGGER `CAPI-TEST`.`addresses_ai` AFTER INSERT ON `CAPI-TEST`.`addresses` FOR EACH ROW
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 '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`, `username`,`revision`,  `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`, `username`, `revision`,  `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.*

OR the most recent version I tested for the update trigger:

DELIMITER $$
CREATE TRIGGER addresses_au
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`, `vUser`, `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 ;
FROM `addresses` AS d WHERE d.AddressID = OLD.AddressID
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.