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.
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!