Using Trigger to update table in another database
20,010
Solution 1
Try this;
CREATE TRIGGER after_update_user
AFTER UPDATE ON db_test.user FOR EACH ROW
UPDATE TABLE db_testplus.user
SET name = NEW.name
WHERE id = NEW.id;
Omitting begin-end keywords worked for me.
Solution 2
Could you please check below
AFTER UPDATE ON db_test.user FOR EACH ROW
BEGIN
UPDATE TABLE db_testplus.user
SET name = NEW.name
WHERE id = NEW.id
END;
Author by
Abhishek
Updated on December 04, 2020Comments
-
Abhishek over 3 years
im using the following trigger to update the user table in another database in mysql 5.0.7 The creation of trigger gives no error but upon updating the user table in the first database the trigger is not working. Any suggestions?
DELIMITER $$ DROP TRIGGER IF EXISTS after_update_user; CREATE TRIGGER after_update_user; AFTER UPDATE ON db_test.user FOR EACH ROW; BEGIN UPDATE TABLE db_testplus.user; SET name = NEW.name; WHERE id = NEW.id; END $$ DELIMITER ;
I also used this code without the semicolons but still the same
DELIMITER $$ DROP TRIGGER IF EXISTS after_update_user CREATE TRIGGER after_update_user AFTER UPDATE ON db_test.user FOR EACH ROW BEGIN UPDATE TABLE db_testplus.user SET name = NEW.name WHERE id = NEW.id END; $$ DELIMITER ;
Finally the code that worked
delimiter | DROP TRIGGER IF EXISTS after_update_user| CREATE TRIGGER after_update_user AFTER UPDATE ON db_test.user FOR EACH ROW BEGIN UPDATE db_testplus.user SET name = NEW.name WHERE id = NEW.id; END; | delimiter ;
-
Fahim Parkar over 12 yearsdid you used
;
afterEND
or afterWHERE id = NEW.id
.. I am asking as your update says you are using afterWHERE id = NEW.id
... Try with adding;
after END as I have shown... -
Abhishek over 12 yearsDoes it matter what Storage Engine is being used. Im using MyISAM
-
Fahim Parkar over 12 yearsnot sure, let me check on google... I am not much in MYSQL but I share that much I know...
-
Fahim Parkar over 12 years
-
Fahim Parkar over 12 yearsmore links stackoverflow.com/questions/6765258/…
-
Abhishek over 12 yearsThanks a lot. I found the problem it was about using delimiters after DROP statement.