0

I am trying to create a TRIGGER that will update a table after updating another table. I need it so that if a number in the number column is updated, then this needs to be recorded in another table. This is what I have so far..

DELIMITER $$
CREATE TRIGGER record_update_to_user_number
    AFTER UPDATE ON user FOR EACH ROW
    IF (NEW.number != OLD.number) THEN//if new updated number is not equal t                                    to  number currently in the table then..

        DECLARE insertednumber TINYINT;  //variable to store updated num

        SET insertednumber = NEW.number;

        INSERT INTO user_changes (username, currentdate, numberchange)
        VALUES (current_user(), CURDATE(), insertednumber);
    END IF;
    DELIMITER ;

Test update to table:

UPDATE user SET number='69' WHERE number='68'; //test update to table

As you can see, I'm trying to store the newly inserted user number into a variable which will then be used to populate the insertednumber column of the user_changes table. Any help would be greatly appreciated, thanks!

Bobby Orange
  • 1
  • 2
  • You seem to pretty much have it. What is the actual problem you are having? – Kickstart Feb 02 '16 at 15:21
  • Apologies, I didn't even put the error with the question. The error is: Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect. – Bobby Orange Feb 02 '16 at 15:25
  • The issue appears to be an option in MySQL. Your UPDATE testing statement is the issue which is triggering it (not the actual TRIGGER you are trying to test). This might give you a few more details - http://stackoverflow.com/questions/11448068/mysql-error-code-1175-during-update-in-mysql-workbench – Kickstart Feb 02 '16 at 15:31
  • Gotcha. Now when I update the number it doesn't insert to the userchanges table, but updates the user table. It's like the trigger isn't recognising the condition? – Bobby Orange Feb 02 '16 at 15:35
  • Can you post your table declares and a couple of sample lines please? – Kickstart Feb 02 '16 at 15:37
  • Here you go: CREATE TABLE user ( id INT, code CHAR(2), number TINYINT NULL, CONSTRAINT take_fk1 FOREIGN KEY (no) REFERENCES person(no), CONSTRAINT take_fk2 FOREIGN KEY (code) REFERENCES session(code)); & my new table that stores the changed data is: CREATE TABLE userchanges ( username VARCHAR(30), currentdate DATE, insertednumber TINYINT); – Bobby Orange Feb 02 '16 at 15:42
  • Few minor things. Your trigger refers to a columns called numberchange while the column is called insertednumber . Also the table is referred to as user_changes when it is defined as userchanges – Kickstart Feb 02 '16 at 15:54

1 Answers1

0

Here is your answer: 

SET SQL_SAFE_UPDATES=0;

Error (Error Code: 1175) during executing update command on table using MySQL Workbench 5.2

Community
  • 1
  • 1
TomerM
  • 335
  • 1
  • 7
  • Thanks, but this wasn't the main issue. The main issue is my trigger isn't recognising the condition as it should. it updates the first table like it should but doesn't record it in the changes table. – Bobby Orange Feb 02 '16 at 15:45