CODEWITHSUNDEEP
javamysql
Priv
Priv

Reputation: 840

Execute MySQL query on certain action

I have two tables (a and b). The question is simple: I want to detect when a row is inserted into table b and then execute a certain query for table a. Is it possible to do this within SQL, or do I need to do it in Java?

The situation:

CREATE TABLE `a` (
`id` INT NOT NULL AUTO_INCREMENT,
`hasb` BOOLEAN NOT NULL,
...
PRIMARY KEY ( `id` )
);

CREATE TABLE `b` (
`id` INT NOT NULL AUTO_INCREMENT,
...
`a` INT NOT NULL,
PRIMARY KEY ( `id` ),
FOREIGN KEY ( `a` ) REFERENCES `a` ( `id` ) ON DELETE CASCADE,
);

If a row is inserted into table b, I want to ensure that hasb in the corresponding row in a is set to true. I do not want to set it back to false when the rows in table b are deleted.

Upvotes: 0

Views: 177

Answers (1)

forpas
forpas

Reputation: 164194

You can do it with this trigger:

CREATE TRIGGER ins_b AFTER INSERT ON b
FOR EACH ROW
BEGIN
  UPDATE a 
  SET hasb = true
  WHERE id = NEW.a;
END;

See the demo.

Upvotes: 1

Related Questions