0

I'm trying to create a trigger which adds an old value to a table with the new value next to it.

For example, if someone were to update their address, I'd like the trigger to add the old address to the table, followed by the new address, then add a timestamp.

The tables:

CREATE TABLE AccountDetails (
    accountID INTEGER AUTO_INCREMENT,
    firstName CHAR(40) NOT NULL,
    lastName CHAR(40) NOT NULL,
    userName VARCHAR(25) UNIQUE,
    userPassword VARCHAR(264),
    emailAddress VARCHAR(30) UNIQUE,
    dateOfBirth DATE NOT NULL,
    phoneNo VARCHAR(20) NOT NULL,
    address1 VARCHAR(50) NOT NULL,
    address2 VARCHAR(50) NOT NULL,
    PRIMARY KEY (accountID)
)  ENGINE INNODB;

CREATE TABLE OldDetails (
    accountID INTEGER NOT NULL,
    oldValue VARCHAR(300),
    newValue VARCHAR(300),
    dateModified DATE,
    PRIMARY KEY (accountID),
    FOREIGN KEY (accountID)
        REFERENCES AccountDetails (accountID)
)  ENGINE INNODB;

The main part I am struggling with is selecting the value that was changed in the update sequence. Thank you all for your help!

  • Can you edit your question and add the trigger code that you have so far? There are already several MySQL audit trigger questions on the site – Philᵀᴹ Sep 9 at 11:24
  • Search for what others have done for an "audit trail" or "history". – Rick James Sep 9 at 20:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.