MySQL on insert in table update field in another table?

Question

I'm creating one PHP script and I need to update table Topics when someone insert something in table TopicAnswers.

Is there any SQL query to do something like this, for example something as FOREIGN KEY?

UPDATE:

mysql> SHOW CREATE TABLE MintForum.Topics\G
*************************** 1. row ***************************
       Table: Topics
Create Table: CREATE TABLE `topics` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `CategoryID` int(10) unsigned NOT NULL,
  `UserID` int(10) unsigned NOT NULL,
  `TopicTitle` varchar(255) NOT NULL,
  `TopicQuestion` longtext NOT NULL,
  `Visits` varchar(255) NOT NULL,
  `Answers` varchar(255) NOT NULL,
  `CreateDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TI
MESTAMP,
  PRIMARY KEY (`ID`),
  KEY `CategoryID` (`CategoryID`),
  KEY `UserID` (`UserID`),
  CONSTRAINT `topics_ibfk_1` FOREIGN KEY (`CategoryID`) REFERENCES `categories`
(`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `topics_ibfk_2` FOREIGN KEY (`UserID`) REFERENCES `users` (`ID`) ON
 UPDATE CASCADE,
  CONSTRAINT `topics_ibfk_3` FOREIGN KEY (`CategoryID`) REFERENCES `categories`
(`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `topics_ibfk_4` FOREIGN KEY (`UserID`) REFERENCES `users` (`ID`) ON
 UPDATE CASCADE,
  CONSTRAINT `topics_ibfk_5` FOREIGN KEY (`CategoryID`) REFERENCES `categories`
(`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `topics_ibfk_6` FOREIGN KEY (`UserID`) REFERENCES `users` (`ID`) ON
 UPDATE CASCADE,
  CONSTRAINT `topics_ibfk_7` FOREIGN KEY (`CategoryID`) REFERENCES `categories`
(`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `topics_ibfk_8` FOREIGN KEY (`UserID`) REFERENCES `users` (`ID`) ON
 UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)


mysql> SHOW CREATE TABLE MintForum.TopicAnswers\G
*************************** 1. row ***************************
       Table: TopicAnswers
Create Table: CREATE TABLE `topicanswers` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `TopicID` int(10) unsigned NOT NULL,
  `UserID` int(10) unsigned NOT NULL,
  `TopicAnswer` longtext NOT NULL,
  `PostDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIME
STAMP,
  PRIMARY KEY (`ID`),
  KEY `TopicID` (`TopicID`),
  KEY `UserID` (`UserID`),
  CONSTRAINT `topicanswers_ibfk_1` FOREIGN KEY (`TopicID`) REFERENCES `topics` (
`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `topicanswers_ibfk_2` FOREIGN KEY (`UserID`) REFERENCES `users` (`I
D`) ON UPDATE CASCADE,
  CONSTRAINT `topicanswers_ibfk_3` FOREIGN KEY (`TopicID`) REFERENCES `topics` (
`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `topicanswers_ibfk_4` FOREIGN KEY (`UserID`) REFERENCES `users` (`I
D`) ON UPDATE CASCADE,
  CONSTRAINT `topicanswers_ibfk_5` FOREIGN KEY (`TopicID`) REFERENCES `topics` (
`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `topicanswers_ibfk_6` FOREIGN KEY (`UserID`) REFERENCES `users` (`I
D`) ON UPDATE CASCADE,
  CONSTRAINT `topicanswers_ibfk_7` FOREIGN KEY (`TopicID`) REFERENCES `topics` (
`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `topicanswers_ibfk_8` FOREIGN KEY (`UserID`) REFERENCES `users` (`I
D`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Insert Query:

INSERT INTO TopicAnswers (TopicID, UserID, TopicAnswer) VALUES (:TopicID, :UserID, :TopicAnswer);

Update Topics Query:

UPDATE Topics SET Answers = Answers + 1;

New Problem: How I can implement this query:

UPDATE Topics SET Answers = Answers + 1 WHERE ID = TopicID;

instead of:

UPDATE Topics SET Answers = Answers + 1

Where TopicID must be TopicID of just inserted row?

SOLVED!

I used :

UPDATE Topics SET Answers = Answers + 1 WHERE ID = NEW.TopicID;

Answer 1

Something like this should do the trick.

DELIMITER $$
CREATE TRIGGER My_Trigger -- name is arbitrary
AFTER INSERT ON TopicAnswers
FOR EACH ROW
BEGIN
  UPDATE Topics
    SET Answers = Answers + 1
    WHERE ID = NEW.TopicID;  -- The OP figured this line out for himself.
END;
$$
DELIMITER ;

I don't have your data, but it should get you started.

Take the trouble to go to the Help Centre, take the tour and check out the "help us to help you" blog. This forum is a great resource and you will get all the more out of it if you take the trouble to read the guidelines (not that there's anything wrong with your post). p.s. welcome to the forum.